Format/Transpose table data to Columns and Rows.
-
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.
-
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 )
-
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
-
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...
-
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 = ''
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)
-
Thanks, I'll give it a whirl.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply