Format/Transpose table data to Columns and Rows.

Question

Post reply

Format/Transpose table data to Columns and Rows.

diesel

SSC Veteran

Points: 281
More actions
April 12, 2005 at 7:58 am

#362393

Hi there,
I have some data as shown below that I need to format in a particular way. I'd like to use TSQL if at all possible for speed and re-use. The test data looks as follows:
Row,Col,Value
0,1,Q1Answer|Q1
1,1,Test1
2,1,Test2
3,1,Test3
4,1,Test4
5,1,Test5
0,2,Q2Answer|Q2
1,2,Test1
2,2,Test2
3,2,Test3
4,2,Test4
5,2,Test5
0,3,Q3Answer|Q3
1,3,Test1
2,3,Test2
3,3,Test3
4,3,Test4
5,3,Test5
0,4,Q4Answer|Q4
1,4,30/03/2005
2,4,29/04/2005
3,4,30/03/2005
4,4,28/12/2004
5,4,30/12/2009
0,5,Q5Answer|Q5
1,5,Male
2,5,Male
3,5,Female
4,5,Female
5,5,Male
Row 0 contains the headers I would like to use for my columns. So, Row 0, Column 1 should be Q1Answer|Q1. Row 1- 5 in Col 1 will be the data in the columns and so on...
This is how I would like the data:
Q1Answer|Q1,Q2Answer|Q2,Q3Answer|Q3,Q4Answer|Q4,Q5Answer|Q5
Test1,Test1,Test1,30/03/2005,Male
Test2,Test2,Test2,29/04/2005,Male
etc....
Help would be appreciated. Thanks.

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

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

Craig Carns Valued Member Points: 63 More actions · Answer 1

I assume you are getting this data as a file...if so I would do it in a few steps

1) Stuff the data into a table(temp) so it looks like your original data file (Use DTS ).

2) Use a nested CURSOR to run through the data and create and execute dynamic INSERT statements ( 1st cursor to select current row and 2nd to select all the columns of current row )

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 2

I agree with Craig for step 1 -- make sure the data is entered into a 3-column table.

Then run something like

SELECT

(select value from DataTable dt1 where dt1.row = dt.row and dt1.col = 1)

, (select value from DataTable dt2 where dt1.row = dt.row and dt1.col = 2)

, (select value from DataTable dt3 where dt1.row = dt.row and dt1.col = 3)

, (select value from DataTable dt4 where dt1.row = dt.row and dt1.col = 4)

, (select value from DataTable dt5 where dt1.row = dt.row and dt1.col = 5)

FROM DataTable dt

ORDER BY dt.Row

This produces a five-column result set as you desire. If you really want the comma-delimited version, you could concatenate the select subqueries with commas (...+ ',' + ...) or you could run the whole thing in query analyzer and save it to a csv file. The query should run faster than a cursor method.

Hope this helps,

Scott Thornburg

diesel SSC Veteran Points: 281 More actions · Answer 3

Thanks guys. I was using VB to build the nested select dynamically but was hoping there was another way to do it. The data is in a table already which makes life easier. The table is being used for reporting so the number of rows and columns will vary for each report. I guess there is no way to do the query dynamically with SQl then...

Don Avery SSC Enthusiast Points: 140 More actions · Answer 4

How about using Dynamic SQL - not the greatest for security, but it does work! I would wrap it up in a proc.

DECLARE @sql varchar(8000)

SET @sql = ''

SELECT @sql = CASE @sql

WHEN '' THEN ISNULL(' Max(CASE [Col] WHEN ' + CAST([Col] as varchar(5)) + ' THEN [Value] ELSE Null END) as ' + QUOTENAME([Value]), '')

ELSE @sql + ',' + CHAR(13) + CHAR(10) + ISNULL(' Max(CASE [Col] WHEN ' + CAST([Col] as varchar(5)) + ' THEN [Value] ELSE Null END) as ' + QUOTENAME([Value]), '')

END

FROM dbo.Test

WHERE Row = 0

ORDER BY Col

SELECT @sql = 'SELECT [Row],' + CHAR(13) + CHAR(10) + @sql + CHAR(13) + CHAR(10) +

' FROM [Test]' + CHAR(13) + CHAR(10) +

' WHERE [Row] > 0' + CHAR(13) + CHAR(10) +

' GROUP BY [Row]' + CHAR(13) + CHAR(10) +

' ORDER BY [Row]'

EXEC(@sql)

diesel SSC Veteran Points: 281 More actions · Answer 5

diesel

SSC Veteran

Points: 281

April 19, 2005 at 6:05 am

#553405

Thanks, I'll give it a whirl.