Change row values to columns

Question

Post reply

Change row values to columns

puja63

SSC Enthusiast

Points: 195
More actions
July 10, 2013 at 12:24 pm

#277254

Hello,
I'm looking for some help with SQL code.
I have a SQL query with many JOINS which returns three or more AccountIDs for a customer, so right now it's like this:
CustomerId CustomerName AcctId ManyOtherColumnsForCustomer
Cust1 Peter 123
Cust1 Peter 345
Cust1 Peter 567
I want to change it to look like this:
CustomerId CustomerName AcctId1 AcctId2 AcctId3
Cust1 Peter 123 345 567
Can someone pelase help me with code to get data as in the 2nd output? Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

keebler96 Old Hand Points: 300 More actions · Answer 1

Here is one way to do it.

--sample table and data

create table custtest (customerID varchar(50), customername varchar(100), acctID integer)

INsert into custtest

select 'Cust1', 'Peter', 123

union

select 'Cust1', 'Peter', 345

union

select 'Cust1', 'Peter', 567

--pivot query

select * from

(select customerID, customername, acctid, 'Acct' + convert( varchar(20), RANK() OVER

(PARTITION BY customerID ORDER BY acctid DESC)) AS Ranked from custtest) as p

pivot

(

max(acctID) for Ranked in ([Acct1], [Acct2], [Acct3]) ) as pvt

Sean Lange SSC Guru Points: 286589 More actions · Answer 2

Here is another way...

create table #Something

(

CustomerID char(5),

CustomerName char(5),

AcctID int

)

insert #Something

select 'Cust1', 'Peter', 123 union all

select 'Cust1', 'Peter', 345 union all

select 'Cust1', 'Peter', 567;

with OrderedRows as

(

select CustomerID, CustomerName, AcctID, ROW_NUMBER() over(Order by AcctID) as RowNum

from #Something

)

select CustomerID, CustomerName,

MAX(case when RowNum = 1 then AcctID else null end) as AcctID1,

MAX(case when RowNum = 2 then AcctID else null end) as AcctID2,

MAX(case when RowNum = 3 then AcctID else null end) as AcctID3

from OrderedRows

group by CustomerID, CustomerName

drop table #Something

Both solutions will only work if there are a maximum of 3 AcctIDs per CustomerID. If you need this to be more dynamic, take a look at the articles in my signature about cross tabs and specifically dynamic cross tabs.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 734667 More actions · Answer 3

PIVOT is your solution. There's one way above, but play with the command and see what it can do for you.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

puja63 (7/10/2013)
Hello,
I have a SQL query with many JOINS which returns three or more AccountIDs for a customer...

Because it seems you have an unknown number of AccountIDs for a customer, you may need to use Dynamic SQL for this.

You can build up a PIVOT query (ala Keebler) or a cross tab query (ala Sean) to make this work.

Here's an article on dynamic cross tab queries for your reference:

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

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

This can be achieved using PIVOT...

Refer this link:

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/