'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.

Current Table structure

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.

Desired output

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

sqlsql-serverpivottranspose


Solution 1:[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 :)

Solution 2:[2]

with ug as (select * from Graduates where Degree Title LIKE "B%") with pg as (select * from Graduates where Degree Title LIKE "M%") with pg1 as (select * from Graduates where Degree Title LIKE "P%")

select ug.id as Graduate ID , ug.Degree Title as Degree Title 1 , ug. Graduation Year as Graduation Year 1 , pg1.Degree Title as Degree Title 2 , pg1. Graduation Year as Graduation Year 2 , pg2.Degree Title as Degree Title 3 , pg2. Graduation Year as Graduation Year 3 from ug left join pg1 on ug.Graduate ID= pg1.Graduate ID left join pg2 on ug.Graduate ID= pg2.Graduate ID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Community
Solution 2 Sridevi R

Related Questions