SQL Transpose Rows to undefined number of columns

问题:

I have a table containing graduate ids, degree titles and graduation years. The table can contain any number of rows for each graduate.

I would like to create a view that would transpose all the rows into as many columns as required, with 'null' being entered in columns where there is no data.

I have seen this question asked quite a few times on here but I'm afraid they have either been not marked as answered or I don't understand the solution. So I have asked the question again in the hope that using my table structure I will better understand the solution.

Any help as always is greatly appreciated.

Edit: This doesn't have to be a view

回答1:

The generic ways of doing this is well described in this answer, but applying that to more than one column in a dynamic fashion when you want the columns in a certain order (degree1, year1, degree2, year2, etc...) can be a bit tricky.

This is one way to do it and as I believe the code is pretty self-explanatory if you have looked at the answer I linked above I won't explain it further:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @title_cols AS NVARCHAR(MAX)
DECLARE @year_cols AS NVARCHAR(MAX)
DECLARE @header AS NVARCHAR(MAX)

SELECT 
    @title_cols = ISNULL(@title_cols + ',','') + QUOTENAME(rn),
    @year_cols  = ISNULL(@year_cols + ',','') + QUOTENAME(CONCAT('y',rn)),
    @header = ISNULL(@header + ',','') + CONCAT('MAX(',QUOTENAME(rn) ,') AS "Degree Title ', rn, '", MAX(',QUOTENAME(CONCAT('y',rn)) ,') AS "Graduation Year ', rn, '"')
FROM (
  SELECT DISTINCT 
    rn = ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Degree Title], [Graduation Year]) 
  FROM Graduates
) GRADS

SET @sql =
  N'SELECT [Graduate ID], ' + @header + '
    FROM (
       SELECT *,
          title = ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Graduation Year]),
          year = CONCAT(''y'',ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Graduation Year])) 
       FROM Graduates) g
    PIVOT(MAX([Degree Title]) FOR title IN (' + @title_cols + ')) AS Titles
    PIVOT(MAX([Graduation Year]) FOR year IN (' + @year_cols + ')) AS Years
    GROUP BY [Graduate ID]'

EXEC sp_executesql @sql

Sample SQL Fiddle

The query uses the concat() function which is available in SQL Server 2012 and later, so if you are using an older version you would have to change that part to a "normal" string concatenation with casts.

I'm sure the query can be improved in many ways, but I'll leave that as an exercise :)