question

jim158 avatar image
jim158 asked

Transposing rows to columns in SQL Server 2008 R2

Hello I have a requirement to transpose rows to columns on a simple table. A sample of the present output looks like this : Groups Subjects ------ ---------- 4O1 Chemistry 4O1 Geography 4O1 History 4O2 Chemistry 4O2 German 4O2 Spanish ... and I'm trying to display it like this : 401 402 --------- --------- Chemistry Chemistry Geography German History Spanish The table is called "teaching" , and it has only two fields, "Groups" and "Subjects". Now, looking around on the web, I've seen plenty of examples where PIVOT , ROW_NUMBER() OVER(PARTITION and MAX are used, but is seems these can only be used when the amount of required columns is fixed, and can be hard-coded. In this case, (see the first output), the "Groups" can vary (they can change, or more can be added, depending on circumstances outside of my control), so the code would need to be able to handle this. If anyone wants to have a go, here is the SQL to create and populate the recordset : BEGIN IF OBJECT_ID('teaching') IS NOT NULL DROP TABLE teaching CREATE TABLE teaching (Groups NVARCHAR(20) NOT NULL, Subjects NVARCHAR(20) NOT NULL) INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Chemistry') INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Geography') INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'History') INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'Chemistry') INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'German') INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'Spanish') END Thanks in advance! Jim
sql-server-2008-r2columnsrow
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lukasz8519 avatar image
Lukasz8519 answered
You can use case, the simplest solutions
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Which won't actually help with the dynamic requirement...
0 Likes 0 ·
Amen to that.
0 Likes 0 ·
@Lukasz8519 , Just to clarify, the use of CASE is one of the simplest solutions but it cannot be done using just CASE. It will still require the use of an character-based aggregation and a GROUP BY. It will also need to be dynamic in order to account for any additional [Groups] that may appear in the data.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
A solution using PIVOT could be something like this: DECLARE @sql VARCHAR(MAX) DECLARE @ColumnList VARCHAR(MAX) SELECT @ColumnList = STUFF((select DISTINCT(',['+Groups+']') FROM teaching ORDER BY 1 FOR XML PATH (''), type).value('.','varchar(max)'),1,1,'') --SELECT @ColumnList -- for debugging, to see what list of columns is built up SELECT @sql = 'SELECT RowNum, ' + @ColumnList + ' FROM ( SELECT Groups, Subjects, ROW_NUMBER() OVER (PARTITION BY Groups ORDER BY Subjects) AS RowNum FROM teaching ) AS t PIVOT (MAX(Subjects) FOR Groups IN (' + @ColumnList+ ')) AS p ORDER BY RowNum' --SELECT @sql -- for debugging, to see what the SELECT statement looks like EXEC (@sql) This will handle any extra rows being added, and extra groups being created. Uncomment the two SELECT lines to see what the individual bits being assembled are.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas Rushton, thank you so much! It worked a treat, first time too! Jim
0 Likes 0 ·
jim158 avatar image
jim158 answered
Thomas Rushton, You solution works well, and some of the grid contents contain NULL values (they show the word NULL). Is there any way to update the columns to change the NULLS to '' (blanks)? ...similar to `UPDATE @columns SET [ColumnName] = '' WHERE [ColumnName] IS NULL ` ? I can't work out how to do that. I could stop the NULL values further down the stream, but it would be nice to do it at the SQL level. Many thanks.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you'd post an update to the fine readily consumable data that you created in your original post, I'll demonstrate a different method that can be faster than the PIVOT method. It will return blanks instead of NULLs without having to do a post update.
1 Like 1 ·
jim158 avatar image
jim158 answered
Hi Jeff, Thanks for your offer. Here is a typical sample of what would be in the table called "teaching" : ![alt text][1] ...and the script to get the records in : IF OBJECT_ID('teaching') IS NOT NULL DROP TABLE teaching CREATE TABLE teaching (Groups NVARCHAR(20) NOT NULL, Subjects NVARCHAR(20) NOT NULL) INSERT INTO teaching (Groups, Subjects) VALUES ('4En', 'English') INSERT INTO teaching (Groups, Subjects) VALUES ('4Ma', 'Maths') INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Biology') INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Chemistry') INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'History') INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'Physics') ...and when I run the code provided by Thomas Rushton above, this is the result, which shows the null values : ![alt text][2] [1]: /storage/temp/4075-temp1.jpg [2]: /storage/temp/4076-temp2.jpg Thanks, Jim

temp1.jpg (15.8 KiB)
temp2.jpg (17.3 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
@jim158 , Thanks for posting the readily consumable data. Just because it's my habit, I've simplified that code a bit. Since the columns are defined as NVARCHAR(), I made the literals match that data-type. IF OBJECT_ID('tempdb..#teaching') IS NOT NULL DROP TABLE #teaching ; CREATE TABLE #teaching ( Groups NVARCHAR(20) NOT NULL ,Subjects NVARCHAR(20) NOT NULL ) ; INSERT INTO #teaching (Groups, Subjects) VALUES (N'4En', N'English') ,(N'4Ma', N'Maths') ,(N'4O1', N'Biology') ,(N'4O1', N'Chemistry') ,(N'4O1', N'History') ,(N'4O2', N'Physics') ; Here's the non-dynamic version of the "CROSS TAB" method. WITH cteGroupRows AS ( SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Groups ORDER BY Subjects) ,Groups ,Subjects FROM #teaching ) SELECT [4En] = MAX(CASE WHEN Groups = N'4En' THEN Subjects ELSE N'' END) ,[4Ma] = MAX(CASE WHEN Groups = N'4Ma' THEN Subjects ELSE N'' END) ,[4O1] = MAX(CASE WHEN Groups = N'4O1' THEN Subjects ELSE N'' END) ,[4O2] = MAX(CASE WHEN Groups = N'4O2' THEN Subjects ELSE N'' END) FROM cteGroupRows GROUP BY RowNum ORDER BY RowNum ; You can learn how it works and what the performance implications are at the following article: [Cross Tabs and Pivots, Part 1 – Converting Rows to Columns][1] Here's the dynamic version: DECLARE @SQL NVARCHAR(MAX) ; --===== Create the dynamic part of the SQL SELECT @SQL = ISNULL(@SQL+NCHAR(10)+SPACE(8)+N',',' ') + REPLACE(REPLACE( '[< >] = MAX(CASE WHEN Groups = N"< >" THEN Subjects ELSE N"" END)' ,'"','''') ,'< >',Groups) FROM (SELECT DISTINCT Groups FROM #teaching) d ; --===== Encapsulate the dynamic SQL into the static SQL SELECT @SQL = ' WITH cteGroupRows AS ( SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Groups ORDER BY Subjects) ,Groups ,Subjects FROM #teaching ) SELECT '+@SQL+' FROM cteGroupRows GROUP BY RowNum ORDER BY RowNum ;' ; --===== Print the dynamic SQL for posterity sake and then execute it. PRINT @SQL; EXEC (@SQL); You can learn more about the dynamic version at the following article. It's a bit longer than the dynamic PIVOT code in this case but CROSS TABs make it a whole lot easier to do row and column totals for numeric data as well as auto-magically getting 0's and blanks in the right places without extra effort. [Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs][2] [1]: http://www.sqlservercentral.com/articles/T-SQL/63681/ [2]: http://www.sqlservercentral.com/articles/Crosstab/65048/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden, Thank you very much for the two sets of code and the very useful link too. I have opted for the dynamic option. Jim
0 Likes 0 ·
My pleasure. Glad to help and thank you for the feedback.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.