question

You can use case, the simplest solutions

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.

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.

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

@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/