Need to pivot the table.

Question

Post reply

Need to pivot the table.

Deeps123

SSC Rookie

Points: 30
More actions
April 10, 2011 at 10:20 pm

#145075

Hi there,
I have a table
Number1 Output
1 x
1 y
1 z
2 a
2 b
Required out put in new table
number1 Output1 Ouput2 Output3 Output4
1 x y z NULL
2 a b NULL NULL
Thanks in advance.

Viewing 9 posts - 1 through 9 (of 9 total)

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

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

Read the following...

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

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

Deeps123 SSC Rookie Points: 30 More actions · Answer 2

Hi Guys,

i got it working..............

Just rank the records. and then use Pivot operator in SQL 2005 to move the column values into single row.

Cheeers...........

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

Deeps123 (4/12/2011)
Hi Guys,
i got it working..............
Just rank the records. and then use Pivot operator in SQL 2005 to move the column values into single row.
Cheeers...........

... until you have more columns to 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)

Carlos Gonzalez-213247 Grasshopper Points: 10 More actions · Answer 4

Hello,

is it possible for you to post the code that you used in order to solve this issue. i have a similar problem that i need to tackle.

Thanks,

C.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Try looking at the article that Jeff posted and wrote. It will be MUCH better for you to learn the topic instead of a quick fix shortcut and be stumped again in 6 months when you have to do something similar.

_______________________________________________________________

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/

Deeps123 SSC Rookie Points: 30 More actions · Answer 6

Below is the code which I used

SELECT

Pvt.KeyColumn,

Pvt.[1] Column1,

Pvt.[2] Column2,

Pvt.[3] Column3,

Pvt.[4] Column4,

Pvt.[5] Column5,

Pvt.[6] Column6,

Pvt.[7] Column7,

Pvt.[8] Column8,

Pvt.[9] Column9,

Pvt.[10] Column10,

FROM

(SELECT

Rank,

Table1.KeyColumn,

Table1.Column (to be Pivoted)

FROM Table1

) AS Unpvted

PIVOT

(MAX(Table1.Column)

FOR Rank IN

([1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10]

)

) AS Pvt

Order By Pvt.KeyColumn

But this code only works if you know the number of columns to be pivoted. I mean to say if the columns keeps on growing this won't work. Bottom line is that this code is fine if u have fixed number of columns like 10 in this case.

And also you need to rank Table1 based on key column value, and then u can use this code to Pivot.

Cheers.......

Carlos Gonzalez-213247 Grasshopper Points: 10 More actions · Answer 7

Thanks for the code. I ended up using an example shown in the link below since the data that i needed to pivot was string data. Worked quite nicely.

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4ac19fe5-be05-479f-bcc2-8fdfa6c1ae1f

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

Carlos Gonzalez-213247 (6/7/2011)
Thanks for the code. I ended up using an example shown in the link below since the data that i needed to pivot was string data. Worked quite nicely.
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4ac19fe5-be05-479f-bcc2-8fdfa6c1ae1f

That, of course, won't actually pivot data into separate columns. You can use "MAX" instead of "SUM" to pivot text data.

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