SQL Transpose Rows to undefined number of columns

Question

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

Can you please provide sql for table ? – Indra Prakash Tiwari May 19 '15 at 09:40 — Indra Prakash Tiwari, May 19 '15 at 09:40

score 2 · Accepted Answer · edited May 23 '17 at 11:43

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 :)

score 0 · Answer 2 · edited Nov 14 '22 at 21:35

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

SQL Transpose Rows to undefined number of columns

2 Answers2

Linked