Any SQL guru here?

[richardycc]

Can anyone help? I need to write a query to generate a report that will look like this:

Case number First Name Last name DOB
1111111 Bob ABC 8-2-1980
2222222 John Doe 7-2-1980

From a table that looks like this:

Key Element_name Element_value
1, Case number, 1111111
1, First Name , Bob
1, Last Name , ABC
1, DOB , 8-2-1980
2, Case number ,2222222
2, First Name , John
2, Last Name , Doe
2, DOB , 7-2-1980

 

[b4u]

Well, I'm no guru, but I got a not-so-pretty query to work that out ...

Let's say you have the following table definition:

CREATE TABLE mytable (
mykey INT(20) NOT NULL,
element_name CHAR(100) NOT NULL,
element_value CHAR(100) NOT NULL
) ENGINE = InnoDB;

Note: I made it in mysql, the id value is renamed to "mykey" instead of just key, and there is no primary key.

Then I populated it:

insert into mytable (mykey, element_name, element_value) values (1, 'Case number', '1111111');
insert into mytable (mykey, element_name, element_value) values (1, 'First Name', 'Bob');
insert into mytable (mykey, element_name, element_value) values (1, 'Last Name', 'ABC');
insert into mytable (mykey, element_name, element_value) values (1, 'DOB', '8-2-1980');
insert into mytable (mykey, element_name, element_value) values (2, 'Case number', '2222222');
insert into mytable (mykey, element_name, element_value) values (2, 'First Name', 'John');
insert into mytable (mykey, element_name, element_value) values (2, 'Last Name', 'Doe');
insert into mytable (mykey, element_name, element_value) values (2, 'DOB', '7-2-1980');
insert into mytable (mykey, element_name, element_value) values (3, 'Case number', '3333333');
insert into mytable (mykey, element_name, element_value) values (3, 'First Name', 'Ayrton');
insert into mytable (mykey, element_name, element_value) values (3, 'Last Name', 'Senna');
insert into mytable (mykey, element_name, element_value) values (3, 'DOB', '1-2-1985');
insert into mytable (mykey, element_name, element_value) values (4, 'Case number', '4444444');
insert into mytable (mykey, element_name, element_value) values (4, 'First Name', 'JustFirst');
insert into mytable (mykey, element_name, element_value) values (4, 'DOB', '7-5-1995');


And the following query:

select distinct mykey,
(select element_value from mytable tbla where tbla.mykey=tbl.mykey and element_name='Case number') as 'Case number',
(select element_value from mytable tblb where tblb.mykey=tbl.mykey and element_name='First Name') as 'First Name',
(select element_value from mytable tblc where tblc.mykey=tbl.mykey and element_name='Last name') as 'Last name',
(select element_value from mytable tbld where tbld.mykey=tbl.mykey and element_name='DOB') as 'DOB'
from mytable tbl;


Returned:

mykey Case number First Name Last name DOB
1 1111111 Bob ABC 8-2-1980
2 2222222 John Doe 7-2-1980
3 3333333 Ayrton Senna 1-2-1985
4 4444444 JustFirst (null) 7-5-1995


Note that for each record, there must exist 4 entries on the table for each field "Case number", "First Name", "Last name" and "DOB. If any entry fails to exist, it will generate a "null" entry (like you can see in the "mykey=4" record which has "null" in the "Last name" field).

Just remove the "distinct mykey," if you don't want the "mykey" value to be returned.


Hope that helps.

 

[KB]

I don't like that table for querying at all. Here is one way to do it, but it is as inefficient as the table structure:

SELECT DISTINCT B.Element_value As [Case Number], C.Element_value As [First Name], D.Element_value As [Last Name], E.Element_value As DOB
FROM
(SELECT DISTINCT KEY FROM Table) A
INNER JOIN (SELECT Key, Element_value FROM Table WHERE Element_name = 'Case number') B ON B.Key = A.Key
INNER JOIN (SELECT Key, Element_value FROM Table WHERE Element_name = 'First Name') C ON C.Key = A.Key
INNER JOIN (SELECT Key, Element_value FROM Table WHERE Element_name = 'Last Name') D ON D.Key = A.Key
INNER JOIN (SELECT Key, Element_value FROM Table WHERE Element_name = 'DOB') E ON E.Key = A.Key

EDIT: doh somebody beat me, but it looks like they were thinking the same thing.

 

[drebo]

Pretty much terrible and terribly inefficient.

 

[Markbnj]

The table structure is definitely nasty, but chances are the OP isn't in control of it . It sounds like either a homework assignment (in which case the prof is a sadistic bastard), or something from the dark corners of IT's Stupid Archive.

 

[Crusty]

Originally posted by: Markbnj
The table structure is definitely nasty, but chances are the OP isn't in control of it . It sounds like either a homework assignment (in which case the prof is a sadistic bastard), or something from the dark corners of IT's Stupid Archive.

Either way the table should be banished to the trash can

 

[techfuzz]

Agreed with the others--that table is horrible.

As for a possible solution to your question, you could possibly use a CASE (or DECODE) statement in your SELECT.

techfuzz

 

[GilletteCat]

Originally posted by: richardycc
Can anyone help? I need to write a query to generate a report that will look like this:

Case number First Name Last name DOB
1111111 Bob ABC 8-2-1980
2222222 John Doe 7-2-1980

From a table that looks like this:

Key Element_name Element_value
1, Case number, 1111111
1, First Name , Bob
1, Last Name , ABC
1, DOB , 8-2-1980
2, Case number ,2222222
2, First Name , John
2, Last Name , Doe
2, DOB , 7-2-1980

Well, since everybody already agreed that the above is a bunch of nonsense, here is my suggestion, lol:
DB Table (MyTable):
Unique_ID Case_Number First_Name Last_Name DOB

1 1111111 Bob ABC 8-2-1980
2 2222222 John Doe 7-2-1980

The Report:
SELECT Case_Number "Case Number", First_Name "First Name", Last_Name "Last Name", DOB
FROM MyTable

XML:
<ROW>
<UNIQUE_ID>1</UNIQUE_ID>
<CASE_NUMBER>1111111</CASE_NUMBER>
<FIRST_NAME>Bob</FIRST_NAME>
<LAST_NAME>ABC</LAST_NAME>
<DOB>8-2-1980</DOB>
</ROW>
<ROW>
<UNIQUE_ID>2</UNIQUE_ID>
<CASE_NUMBER>2222222</CASE_NUMBER>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Doe</LAST_NAME>
<DOB>7-2-1980</DOB>
</ROW>

As I said, just for giggles

 

[drebo]

Interestingly, the Asterisk Real Time database is laid out in a somewhat similar fashion.

All rows exist in a single table with a unique key and individual indicies for file, category, property, and value. It works well, as the database was devised as a replacement for flat files which were meant to be interpreted on a line-by-line sequential basis. Basically, this way, you get one table with 10,000 rows, as opposed to a 3rd normal form version of the database where you'd have probably 15 tables with 10,000 rows each. The data lends itself well to a linear data model.

So, there are a few applications where this type of layout makes a bit of sense. But, over all, I would very much advise against it.

 

[richardycc]

thanks everyone, I was able to use the example in here to finish my query, I had to join 3 of these tables together, luckily, once I got the basis of it, the rest was pretty easy. I wish this was homework, but this is real life, and I have no control of the structure.