Converting Rows into columns issue

Question

Converting Rows into columns issue

adhikari707

SSChasing Mays

Points: 639
More actions
February 16, 2016 at 3:45 pm

#304789

Hi ,
I need help in a scenario that i am working on where i have to convert rows to columns , i tried using pivot function but not able to reproduce the output that is desired.
Below is the sample of the scenario i am working on.
What would be the best logic to handle this situation ?
Any help would be greatly appreciated.
Customer Table
------------------
CustomerID Country State City
-----------------------------------------------------
1 USA IL Chicago
1 USA WI New Berlin
1 USA TX Austin
2 USA IL Lisle
Output Wanted
----------------
CustomerID Country1 State1 City1 Country2 State2 City2 Country3 State3 City3
--------------------------------------------------------------------------------------------------------------
1 USA IL Chicago USA WI New Berlin USA TX Austin
2 USA IL Lisle NULL NULL NULL NULL NULL NULL

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 1

adhikari707 (2/16/2016)
Hi ,
I need help in a scenario that i am working on where i have to convert rows to columns , i tried using pivot function but not able to reproduce the output that is desired.
Below is the sample of the scenario i am working on.
What would be the best logic to handle this situation ?
Any help would be greatly appreciated.
Customer Table
------------------
CustomerID Country State City
-----------------------------------------------------
1 USA IL Chicago
1 USA WI New Berlin
1 USA TX Austin
2 USA IL Lisle
Output Wanted
----------------
CustomerID Country1 State1 City1 Country2 State2 City2 Country3 State3 City3
--------------------------------------------------------------------------------------------------------------
1 USA IL Chicago USA WI New Berlin USA TX Austin
2 USA IL Lisle NULL NULL NULL NULL NULL NULL

First, have a look at the first link in my signature line below under "Helpful Links" to help us help you more quickly in the future. I'll do for you this time in the following code along with a solution to your problem...

--===== Create the test data.

-- This is NOT a part of the solution!

SELECT *

INTO #Customer

FROM (

SELECT 1,'USA','IL','Chicago' UNION ALL

SELECT 1,'USA','WI','New Berlin' UNION ALL

SELECT 1,'USA','TX','Austin' UNION ALL

SELECT 2,'USA','IL','Lisle'

) d (CustomerID,Country,State,City)

;

--===== Enumerate and then "Pivot" the data as a solution.

-- If you have more than 3 rows per customer, then we'll need to convert to a dynamic solution.

WITH cteEnumerate AS

(

SELECT Seq = ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Country, State, City)

,*

FROM #Customer

)

SELECT CustomerID

,Country1 = MAX(CASE WHEN Seq=1 THEN Country ELSE '' END)

,State1 = MAX(CASE WHEN Seq=1 THEN State ELSE '' END)

,City1 = MAX(CASE WHEN Seq=1 THEN City ELSE '' END)

,Country2 = MAX(CASE WHEN Seq=2 THEN Country ELSE '' END)

,State2 = MAX(CASE WHEN Seq=2 THEN State ELSE '' END)

,City2 = MAX(CASE WHEN Seq=2 THEN City ELSE '' END)

,Country3 = MAX(CASE WHEN Seq=3 THEN Country ELSE '' END)

,State3 = MAX(CASE WHEN Seq=3 THEN State ELSE '' END)

,City3 = MAX(CASE WHEN Seq=3 THEN City ELSE '' END)

FROM cteEnumerate

GROUP BY CustomerID

;

For more information on the CROSSTAB used above, please see the following articles...

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

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

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

adhikari707 SSChasing Mays Points: 639 More actions · Answer 2

Thanks for providing the solution.

will keep in mind next time how to post questions on a forum to get the best help.

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 3

adhikari707 (2/17/2016)
Thanks for providing the solution.
will keep in mind next time how to post questions on a forum to get the best help.

You bet. Thank you for the feedback.

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