T-SQL help to convert rows to columns

Question

Post reply

T-SQL help to convert rows to columns

sangeethu

Grasshopper

Points: 20
More actions
September 8, 2010 at 12:00 pm

#387745

Hi,
We have multiple dealers and multiple locations in our database.A table named dealer_location links dealers with locations which consists of the following entries
Dealer_locationid dealerid locationid
----------------- -------- -----------
1001 101 501
1002 101 502
1003 101 503
1004 101 504
1005 102 501
1006 102 502
where the number of locations is not static.
Now I need to generate a result based on the dealer and his multiple locations.
So the result should be,
dealerid | locationid | locationid | locationid |locationid
101 501 502 503 504
102 501 502 null null
please help me in writing the query.

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab article referenced in my signature.

Give it a try and post back if you get stuck.

Once you figured how it works you might want to read the DynamicCrossTab article as well to get a neat piece of code that would be able to include new locationid's.

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]

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 2

Try this

WITH cteDealer (Dealer_locationid, dealerid, locationid) AS

(

SELECT 1001, 101, 501 UNION ALL

SELECT 1002, 101, 502 UNION ALL

SELECT 1003, 101, 503 UNION ALL

SELECT 1004, 101, 504 UNION ALL

SELECT 1005, 102, 501 UNION ALL

SELECT 1006, 102, 502

)

,

ctePrepare (ROW, dealerid, locationid) AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY dealerid ORDER BY Dealer_locationid), dealerid, locationid

FROM cteDealer

)

SELECT dealerid, [1] AS location1, [2] AS location2, [3] AS location3, [4] AS location4

FROM ctePrepare

PIVOT (MAX(locationid) FOR ROW IN ([1],[2],[3],[4])) AS pvt

sql-programmers SSC-Addicted Points: 494 More actions · Answer 3

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants

http://www.sql-programmers.com/

SQL Sever Performance tuning

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 4

Tarun Jaggi

Hall of Fame

Points: 3365

September 9, 2010 at 3:41 am

#1218459

Use PIVOT TABLE.

sangeethu Grasshopper Points: 20 More actions · Answer 5

Hi Lutz,

Thanks for the reply. I have gone through Cross Tab and Dynamic Cross tab and found that it involves aggregation of a column. But in my case every column is a varchar field and my resultset should be

continues to n number of locations where n is dynamic.

Please guide me.

Regards,

Sangeetha

David Burrows SSC Guru Points: 65142 More actions · Answer 6

sangeethu (9/9/2010)
Hi Lutz,
Thanks for the reply. I have gone through Cross Tab and Dynamic Cross tab and found that it involves aggregation of a column. But in my case every column is a varchar field and my resultset should be
Dealerid | locationid1 | location1_add1 | location1_add2 | location1_city | location1_state | location1_zip| locationid2 | location2_add1| location2_add2 | location2_city | location2_state | location2_zip ................
continues to n number of locations where n is dynamic.
Please guide me.
Regards,
Sangeetha

Use MAX instead of SUM or COUNT

Far away is close at hand in the images of elsewhere.
Anon.

LutzM SSC Guru Points: 107049 More actions · Answer 7

David Burrows (9/9/2010)
sangeethu (9/9/2010)
Hi Lutz,
Thanks for the reply. I have gone through Cross Tab and Dynamic Cross tab and found that it involves aggregation of a column. But in my case every column is a varchar field and my resultset should be
Dealerid | locationid1 | location1_add1 | location1_add2 | location1_city | location1_state | location1_zip| locationid2 | location2_add1| location2_add2 | location2_city | location2_state | location2_zip ................
continues to n number of locations where n is dynamic.
Please guide me.
Regards,
Sangeetha
Use MAX instead of SUM or COUNT

David is right. MAX() will do the trick here.

As a side note: If you would have provided sample data easily spotted as varchar or at least mentioned that you need to deal with character data we could have included that information in our solutions...

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]