Rows to Columns

Question

Post reply

Rows to Columns

highlord1

Grasshopper

Points: 18
More actions
May 30, 2011 at 10:47 am

#241170

Hi,
I have a table with 4 columns
Name Date Value idx
Lucy 12-20-2010 '10 ' 1
Lucy 12-20-2010 '15 ' 2
Lucy 12-20-2010 '13' 3
Lucy 12-20-2010 '12' 4
Luke 12-20-2010 '10 ' 1
Luke 12-20-2010 '15' 2
Luke 12-20-2010 '13' 3
Luke 12-20-2010 '18' 4
I want to diplay this table as
Name Date idx1 idx2 idx3 idx4
Lucy 12-20-2010 '10' '15' '13' '12'
Luke 12-20-2010 '10' '15' '13' '18'
Any help is appreciated.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Are you doing this in SSRS? If so this is a really easy pivot in there.

highlord1 Grasshopper Points: 18 More actions · Answer 2

highlord1

Grasshopper

Points: 18

May 30, 2011 at 10:54 am

#1331689

I'm not using SSRS.

How can iI use pivot?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

http://msdn.microsoft.com/en-us/library/ms177410.aspx

LutzM SSC Guru Points: 107049 More actions · Answer 4

Additionally to the PIVOT recommended above you could use the good old CrossTab approach.

For an example please have a look at the related article referenced in my signature.

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]

highlord1 Grasshopper Points: 18 More actions · Answer 5

highlord1

Grasshopper

Points: 18

May 30, 2011 at 12:46 pm

#1331716

Thank you LutzM!

sqlmaverick Default port Points: 1462 More actions · Answer 6

It is easy to use ssis. Use pivot transformation to do this.

Get that table into particular source transformation and feed that output into sort transformation and then pivot transformation.

In pivot transformation set Name=1 and id=2 and value=3 for input columns and add output columns as shown below in videos. Try to follow these videos in sequence.

Part1: http://www.youtube.com/watch?v=ZoCxAksokJM&feature=related

Part2:http://www.youtube.com/watch?v=8roHvHd5Uk0&feature=related

Part3:http://www.youtube.com/watch?v=y2CKXySrb4A&feature=related

Part4:http://www.youtube.com/watch?v=wTGSTGPY91M&feature=related

sqlmaverick Default port Points: 1462 More actions · Answer 7