How to Flatten Table Resultset

Question

Post reply

How to Flatten Table Resultset

MostInterestingMan

SSCertifiable

Points: 6291
More actions
August 17, 2009 at 11:40 am

#224907

Hi,
I have a table that has data that I need to flatten. This is a non-heirarchy table with customer and orders data as shown in the below example:
CusID Type
11 A
11 B
11 C
22 A
22 B
22 C
My goal is to display the output like the example below:
CustID Type1 Type2 Type3
11 A B C
22 A B C
Your help is greatly appreciated.
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 15 posts - 1 through 15 (of 19 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please search for "PIVOT" or "CROSS TAB" on this site.

You'll find a number of posts where at least one should answer your question.

You could also go directly to one of the two following articles by Jeff Moden:

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

http://www.sqlservercentral.com/articles/Crosstab/65048/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

JamieX Hall of Fame Points: 3763 More actions · Answer 2

I've never been able to get pivot to work for these types of situtations so I've resorted to dynamic SQL in the past. Here's a conversion of something similar I did before. Be damn careful with dynamically generated code like this. If you have data with single quote's, you'd have to do some checks on the type values.

CREATE TABLE #t (CusID INT, [Type] NVARCHAR(20))
INSERT #t
SELECT 11,'A'
UNION ALL SELECT 11,'B'
UNION ALL SELECT 11,'C'
UNION ALL SELECT 22,'A'
UNION ALL SELECT 22,'B'
UNION ALL SELECT 22,'C'
UNION ALL SELECT 22,'D'
UNION ALL SELECT 33,'C'
DECLARE @Q NVARCHAR(MAX)
SELECT @Q = 'SELECT CusID ' + CHAR(10)
;WITH cte([id], [Type]) as
( SELECT row_number() over (order by [Type]) [id], [Type] FROM #t GROUP BY TYPE )
SELECT @Q = @Q + ' ,MAX(CASE WHEN [Type] = ''' + [Type] + ''' THEN ''' + [Type] + ''' ELSE '''' END) [Type' + CAST([id] as NVARCHAR) + '] ' + CHAR(10)
FROM cte
SELECT @Q = @Q + 'FROM #t GROUP BY [CusID]'
--SELECT @Q
EXEC(@Q)
DROP TABLE #t

Produces:

CusID Type1 Type2 Type3 Type4
----------- ----- ----- ----- -----
11 A B C
22 A B C D
33 C

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

another method that concatenates row values

SELECT p1.CusId,

( SELECT replace(type, ' ', '') + ','

FROM yourtable p2

WHERE p2.CusId = p1.CusId

ORDER BY type

FOR XML PATH('') ) AS type

FROM yourtable p1

GROUP BY CusId ;

produces

11 A,B,C,

22 A,B,C,

additional info from here:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 4

Lutz, Jamie, Gah thanks for the info you provided. I did look into a pivot solution but it doesnt solve the problem.

Below is a better example definition and what Im hoping to accomplish:

CREATE TABLE customer(CusID INT, [Name] NVARCHAR(10))

INSERT customer

SELECT 1,'John'

UNION ALL SELECT 2,'Paul'

UNION ALL SELECT 3,'Jon'

UNION ALL SELECT 4,'Mary'

UNION ALL SELECT 5,'Beth'

CREATE TABLE orders (CusID INT, [Product] NVARCHAR(15), [Status] NVARCHAR(15))

INSERT orders

SELECT 1,'Apples','Pending'

UNION ALL SELECT 1,'Oranges','Pending'

UNION ALL SELECT 1,'Lemon','Hold'

UNION ALL SELECT 2,'Lemon','Hold'

UNION ALL SELECT 2,'Oranges','Hold'

UNION ALL SELECT 2,'Apples','Received'

UNION ALL SELECT 3,'Apples','Hold'

UNION ALL SELECT 3,'Oranges','Hold'

UNION ALL SELECT 3,'Lemon','Hold'

UNION ALL SELECT 4,'Lemon','Hold'

UNION ALL SELECT 4,'Oranges','Hold'

UNION ALL SELECT 4,'Apples','Received'

UNION ALL SELECT 5,'Apples','Hold'

UNION ALL SELECT 5,'Oranges','Hold'

UNION ALL SELECT 5,'Lemon','Hold'

The goal is to have the report formated as follows:

cusid name product1 product2 product3 status

----------- ----------- ----------- ----------- ----------- -----------

1 John Apples Oranges Lemon pending

Instead of:

cusid name product status

----------- ---------- --------------- ---------------

1 John Apples Pending

1 John Oranges Pending

1 John Lemon Hold

2 Paul Lemon Hold

2 Paul Oranges Hold

2 Paul Apples Received

...........................................................

4 Mary Apples Received

Thanks again for all your help....

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

What is the business rule that guided you to pick "PENDING" instead of "HOLD" for the following?

cusid name product1 product2 product3 status

----------- ----------- ----------- ----------- ----------- -----------

1 John Apples Oranges Lemon pending

John has pending Apples and Oranges while the Lemon is on hold. What is the business rule for that.

Also, how many products can you have?

It could make a difference... what is the business reason for needing to present the data in a horizontal rather than vertical format?

Last but not least, take a look at the two articles that Lutz suggested for guidance on how to do this without PIVOT.

--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)

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

Jeff,

Thanks for pointing that out. The layout should look like:

cusid name product1 status product2 status product3 status

------ ------ --------- -------- ----------- -------- ----------- -----------

1 John Apples pending Oranges pending Lemon hold

Thanks for taking a look and any help is greatly appreciated.

cos_ta393

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

cos_ta393 (8/18/2009)
Jeff,
Thanks for pointing that out. The layout should look like:
cusid name product1 status product2 status product3 status
------ ------ --------- -------- ----------- -------- ----------- -----------
1 John Apples pending Oranges pending Lemon hold
Thanks for taking a look and any help is greatly appreciated.
cos_ta393

Thanks... that makes more sense... still, I need to know the answers to my other questions, as well. I just want to do this once, if you know what I mean.

--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)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

....
The goal is to have the report formated as follows:
cusid name product1 product2 product3 status
----------- ----------- ----------- ----------- ----------- -----------
1 John Apples Oranges Lemon pending
....

If as you say this is for a report...is there any reason to require column headers?

What happens when the extract doesnt fit on one line of your report, because there are 17 different products/statuses

just being curious...:unsure:

kind regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 9

I certainly do. The dataset here is an example ofcourse however it closely maps to the actual:

1) There are only 3 products types

2) The business reasoning I guess is to address an adhoc need from what I'm told

3) Finally I reviewed Lutz's articles but this solution will be implemented against a 2000 engine

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 10

Gah,

Great point and I raised this in a meeting this afternoon. Everyone just wants a solution at this point.

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

LutzM SSC Guru Points: 107049 More actions · Answer 11

cos_ta393 (8/18/2009)
3) Finally I reviewed Lutz's articles but this solution will be implemented against a 2000 engine
Thanks,

Three things to notice:

a) you posted in a SQL2K5 forum so someone could expect your issue is 2K5 related (just a minor issue though, once you've stated what you're using...).

b) the linked article I pointed you at is not mine so I don't deserve any credits for it... (right, Jeff? 😉 )

c) the solution described in the article works on SQL2K as well (except for the PIVOT section, of course....). So there's no reason not to follow it...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 12

cos_ta393 (8/18/2009)
3) Finally I reviewed Lutz's articles but this solution will be implemented against a 2000 engine
Thanks,

Heh... then the crosstab solution in both articles is perfect for you.

Thanks for answering the other questions... I'll be back.

--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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 13

lmu92 (8/18/2009)
b) the linked article I pointed you at is not mine so I don't deserve any credits for it... (right, Jeff? )

Actually, some good credit is due... you referred the op to some really good articles. 😛

--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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 14

Jeff Moden (8/18/2009)
cos_ta393 (8/18/2009)
3) Finally I reviewed Lutz's articles but this solution will be implemented against a 2000 engine
Thanks,
Heh... then the crosstab solution in both articles is perfect for you.
Thanks for answering the other questions... I'll be back.

Like Lutz pointed out, you might want to be a bit careful to make sure that you post to the correct forum. People tend to get a little fizzled if they build a 2k5 solution on a 2k5 forum only to find out the op needs only 2k. It's a huge waste of your time, as well.

Anyway, thank you the great test data... it sure does make things easy for folks like me. Here's a solution using that test data that will work in 2k for 3 products like you asked.

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

SELECT o.CusID,

c.Name AS Name,

MAX(CASE WHEN o.Product = 'Apples' THEN o.Product END) AS Product1,

MAX(CASE WHEN o.Product = 'Apples' THEN o.Status END) AS Status1,

MAX(CASE WHEN o.Product = 'Oranges' THEN o.Product END) AS Product2,

MAX(CASE WHEN o.Product = 'Oranges' THEN o.Status END) AS Status2,

MAX(CASE WHEN o.Product = 'Lemon' THEN o.Product END) AS Product3,

MAX(CASE WHEN o.Product = 'Lemon' THEN o.Status END) AS Status3

FROM dbo.Orders o

INNER JOIN dbo.Customer c

ON o.CusID = c.CusID

GROUP BY o.CusID, c.Name

ORDER BY o.CusID, c.Name

--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)