Convert/Pivot multiple rows to columns to flatten data SQL Server 2008

[javedi]

Hello

I have the sample data below that I am trying to flatten from a view on SQL Server 2008. I was hoping there is an easy way to do this but I can't find any after Googling. The problem is with rows 4,5 and 6 which need to be in one column under Non-Reusable Item Details. Any idea's on how this can be achieved?


-- TicketNo---------------------Date----------Answer-------Caption
1 CAS-06103-P8V3B0 --13/12/2012 14:56--test --Additional Information
2 CAS-06103-P8V3B0 --13/12/2012 14:56--test --Collection Additional Information
3 CAS-06103-P8V3B0--13/12/2012 14:5--Sofa --Non-Reusable Item Details
4 CAS-06103-P8V3B0--13/12/2012 14:56 --Armchair --Non-Reusable Item Details
5 CAS-06103-P8V3B0--13/12/2012 14:56--Table --Non-Reusable Item Details
6 CAS-06103-P8V3B0--13/12/2012 14:56 --Bed base --Non-Reusable Item Details
7 CAS-06103-P8V3B0--13/12/2012 14:56 --4 --Quantity
8 CAS-06103-P8V3B0--13/12/2012 14:56 --30 --Value
9 CAS-03022-F5S0N7--21/06/2012 11:13 --2 --Quantity
10 CAS-03022-F5S0N7--21/06/2012 11:13 --Sofa --Non-Reusable Item Details
11 CAS-03022-F5S0N7--21/06/2012 11:13 --Other (must specify) --Non-Reusable Item Details
12 CAS-03022-F5S0N7--21/06/2012 11:13 --TV Reusable-- Item Details
13 CAS-03022-F5S0N--21/06/2012 11:13 --Other (must specify) --Reusable Item Details
14 CAS-03022-F5S0N7--21/06/2012 11:13 --tt--Collection Additional Information
15 CAS-03022-F5S0N7--21/06/2012 11:13--15--Value

The output needs to look like this;

TicketNo -------------Date ----------------(Caption field)Additional Information---Collection Additional Information
Non-Reusable Item Details----Quantity---Value
CAS-06103-P8V3B0--13/12/2012 14:56------Test--test--Armchair, Table, Bed base-----------4--------------30

Thank you for any help.

Javedi

 

[drlex]

I found Brad's column here -

http://bradsruminations.blogspot.co.uk/2009/10/making-list-and-checking-it-twice.html

- very useful in a similar situation.


soi là, soi carré

 

[javedi]

Thanks drlex, Good article, is there no simpler way to convert the caption values to columns and the answers values to rows for each ticket number?

 

[thorny00]

How many records are you talking? Are the dashes in the data? Could a CSV file be created then loaded into SQL?

 

[javedi]

The dashes were put in by me to seperate the columns and there are around 1200 records. CSV would be fine, it would open in excel.

Thanks,
Javedi