Denormalized Query of a Normal Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Denormalized Query of a Normal Database

Author

Topic

MegaTrain
Starting Member

16 Posts

Posted - 2004-01-06 : 11:00:48

If my database is normalized, with data like

Customers
Customer#
CustomerName

and

Characteristics
Customer#
Type
Value

Where the type is Hair, Eyes, or Height

I want to get the data back out as


Customer     Hair     Eyes     Height     
Julie        Brown    Hazel    5'6"
Mark         Blonde            6'1"
Brad         Bald     Brown    5'11"

Keep in mind that some of those values might not have records.
How do I do that?
(these aren't my real tables, of course, but this will help me get what I need)

Thanks!

mr_mist
Grunnio

1870 Posts

Posted - 2004-01-06 : 11:03:14

Use a left outer join.

-------
Moo. :)

MegaTrain
Starting Member

16 Posts

Posted - 2004-01-06 : 11:09:19

A left outer join will give me


Julie   Hair   Brown
Julie   Eyes   Hazel
Julie   Height 5'6"
Mark    Hair   Blonde
....

How do I turn it around, to display as columns instead of rows?

MegaTrain
Starting Member

16 Posts

Posted - 2004-01-06 : 11:45:18

I guess this is similar to a crosstab query, but all the crosstab stuff I look up does fancy sums and stuff. This seems like it would be pretty trivial, but I'm having a hard time finding what I need.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-06 : 12:36:30

You need to look-up "Dynamic Crosstab" on this site it is fancy but you want to use a MAX() or MIN() for your text values and it should work.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-07 : 02:11:09

I think you need a whole truckload of subqueries:


SELECT a.CustomerName, 
 (SELECT Value FROM Characteristics b WHERE b.CustomerID = a.CustomerID AND b.Type = 'Hair') AS Hair,
 (SELECT Value FROM Characteristics b WHERE b.CustomerID = a.CustomerID AND b.Type = 'Eyes') AS Eyes,
 (SELECT Value FROM Characteristics b WHERE b.CustomerID = a.CustomerID AND b.Type = 'Height') AS Height
FROM Customers a

This will progressively get slower as the number of customers increases.

OS

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-07 : 08:00:09

Tweak Owais's code a bit:

SELECT a.CustomerName,
Max(CASE WHEN b.Type = 'Hair' THEN b.Value END) AS Hair,
Max(CASE WHEN b.Type = 'Eyes' THEN b.Value END) AS Eyes,
Max(CASE WHEN b.Type = 'Height' THEN b.Value END) AS Height
FROM Customers a
GROUP BY a.CustomerName

This is exactly what drymchaser was suggesting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources