How to Denormalize Data / How to Flatten Normalized Data

Question

How to Denormalize Data / How to Flatten Normalized Data

YSLGuru

SSC-Insane

Points: 21661
More actions
May 4, 2011 at 11:14 am

#147944

NOTE: I did try a search on this but couldnot find what I was looking for.
SCENARIO: How to take many rows which have a fixed max number of rows returned and denormalize it so that each row becomes a column in the query.
DETAILS: In a query that already joins several tables I need to include a table in which the number of rows it returns is fixed (will always be 2 or less) and pivot those so that each value is in its own column.
EX: The below returs 2 or less rows for each customer with the difference being the phone number. The condition on the Join to the PHONENUMBER table ensures a max of 2 rows of phone numbers per customer.
SELECT C.Name, AD.StreetAddr1, AD.StreetAddr2, AD.City, AD.State, AD.Zip, N.PhoneNum
FROM CUSTOMER C Join ADDRESS AD ON C.ID = AD.CustID AND AD.AddressType = 'PRIMARY'
Left Outer Join PHONENUMBER N ON C.ID = P.CustId AND N.PhoneType IN('Home','Office')
How do I re-write this so that I get 1 row per customer with output like this where X is the phone number where PhoneType = 'Home' and Y is the phone number where PhoneType = 'Office':
SELECT C.Name, AD.StreetAddr1, AD.StreetAddr2, AD.City, AD.State, AD.Zip, [X] AS 'HomePhone', [Y] AS 'OfficePhone'
I looked at PIVOT but I don't think its right for this scenbario but I admit I could very well be wrong. Any help even a link to an article/post explaining this would be most welcome.
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hey YSL, with 649 points to your name, you sure must be knowing how to post your question so that you get maximum response. Please post sample data as INSERT INTO script, your table design as CREATE TABLE script, with Indexes if any...Please 🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

For starters, you can use

1. GROUP BY with MAX(CASE END) to get a row turned into columns

2. CROSS APPLY twice (or once) to get rows added as columns to an existing query

3. PIVOT , as you know the values, viz Home and Office

I know, and a lot of good folks here know, how to perform it thro all the 3 ways.. It would largely help us if you provide what I asked you..

HTH

GSquared SSC Guru Points: 260824 More actions · Answer 3

The easiest way I know of to do that would be to join to the phone number table twice, instead of having an "IN" statement. First instance would pull the first type of phone only, second instance the second.

SELECT C.Name,

AD.StreetAddr1,

AD.StreetAddr2,

AD.City,

AD.State,

AD.Zip,

N.PhoneNum as HomePhone,

N2.PhoneNum as OfficePhone

FROM CUSTOMER C

JOIN ADDRESS AD

ON C.ID = AD.CustID

AND AD.AddressType = 'PRIMARY'

LEFT OUTER JOIN PHONENUMBER N

ON C.ID = P.CustId

AND N.PhoneType = 'Home'

LEFT OUTER JOIN PHONENUMBER N2

ON C.ID = P.CustId

AND N2.PhoneType = 'Office';

As an aside, I think the join math for the phones table is iffy. It doesn't reference any column in "N" that would limit it to the customer table or address table value, so would produce a cartesian join. I think it's supposed to be "C.ID = N.CustID", instead of "P.CustID", which looks like a cut-and-paste error from the join above it. But I can't be sure, since I don't have the table definitions.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

ColdCoffee (5/4/2011)
For starters, you can use
1. GROUP BY with MAX(CASE END) to get a row turned into columns
2. CROSS APPLY twice (or once) to get rows added as columns to an existing query
3. PIVOT , as you know the values, viz Home and Office
I know, and a lot of good folks here know, how to perform it thro all the 3 ways.. It would largely help us if you provide what I asked you..
HTH

My apologies, I neglected to mention in my last post that the sample is psdueocode and not an actual example of my DB. Because of an NDA I'm not supposed to post actual code samples so I have to make up samples whenever I do post; very frustrating to say the least.

Thanks for replying

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

Thanks G and yes your right about the reverse on the copy and paste. I try when i can to actually create the dummy tables and data so that I can then produce accurate Tsql sample code but I was short on time and cheated this go around, making the query up on the fly. Because of a NDA I'm not suppose to post actual code and so I always have to make up dummy code.

As for the double joining of the table, thats what I'm doinmg now but I hate to think thats the best method.

Thanks for replying

Kindest Regards,

Just say No to Facebook!

GSquared SSC Guru Points: 260824 More actions · Answer 6

Insofar as "best method", define "best". It gets the job done.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jhorowitz10 SSC Rookie Points: 43 More actions · Answer 7

You might also want to check out this link:

http://www.sqlservercentral.com/articles/T-SQL/63681/

How to easily Converting Rows to Columns using Crosstabs

I hope this helps:

SELECT C.Name,

AD.StreetAddr1,

AD.StreetAddr2,

AD.City,

AD.State,

AD.Zip,

MAX(case WHEN N.PhoneType = 'Home' then

N.PhoneNum End) AS [HomePhone],

MAX(case WHEN N.PhoneType = 'Office' then

N.PhoneNum End) AS [OfficePhone]

FROM CUSTOMER C

JOIN ADDRESS AD

ON C.ID = AD.CustID AND AD.AddressType = 'PRIMARY'

LEFT OUTER JOIN PHONENUMBER N

ON C.ID = N.CustId

GROUP by c.name,

AD.StreetAddr,

AD.StreetAddr2,

AD.City,

AD.State,

AD.Zip

YSLGuru SSC-Insane Points: 21661 More actions · Answer 8

GSquared (5/5/2011)
Insofar as "best method", define "best". It gets the job done.

When I say best I mean the fastest method. I know my DB is on the smaller end of range of VLDB's but even at 250GB I have to watch performance closely as the application that uses the DB is not optimally designed in as far as how it works with the DB.

Thanks again for replying

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 9

jhorowitz10 (5/6/2011)
You might also want to check out this link:
http://www.sqlservercentral.com/articles/T-SQL/63681/
How to easily Converting Rows to Columns using Crosstabs
I hope this helps:
SELECT C.Name,
AD.StreetAddr1,
AD.StreetAddr2,
AD.City,
AD.State,
AD.Zip,
MAX(case WHEN N.PhoneType = 'Home' then
N.PhoneNum End) AS [HomePhone],
MAX(case WHEN N.PhoneType = 'Office' then
N.PhoneNum End) AS [OfficePhone]
FROM CUSTOMER C
JOIN ADDRESS AD
ON C.ID = AD.CustID AND AD.AddressType = 'PRIMARY'
LEFT OUTER JOIN PHONENUMBER N
ON C.ID = N.CustId
GROUP by c.name,
AD.StreetAddr,
AD.StreetAddr2,
AD.City,
AD.State,
AD.Zip

I'll check it out. thanks.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 10

jhorowitz10 (5/6/2011)
You might also want to check out this link:
http://www.sqlservercentral.com/articles/T-SQL/63681/
How to easily Converting Rows to Columns using Crosstabs

With a first post like that, I've just gotta say, "Welcome Aboard!" 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)