Convert rows into N columns in SQL Server

Question

I need to convert this data set into rows

ID  year    reading writing spelling
33087   7   625 620 686
33087   8   544 560 541
33205   7   559 572 497
33205   8   599 560 612
33902   7   500 484 464
33902   8   607 560 686

into this:

ID  year    reading writing spelling year   reading writing spelling 
33087   7   625     620     686      8      544     560     541
33205   7   559     572     497      8      599     560     612
33902   7   500     484     464      8      607     560     686

This my code :

select * from 
(select ID,year,reading 
       from #Table1 NP 
JOIN #table2 CS  ON CS.Id = NP.ID
) as  src
PIVOT
(
sum(reading) for year in ([7],[8])
) as piv

I'm not sure how to get rest of the rows in to the columns.

[This MSDN Documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017) shows exact example like what you are looking for — Qwerty, May 15 '19 at 13:04

Shnugo · Answer 1 · 2019-05-15T08:32:10.080

I'd suggest to use conditional aggregation:

DECLARE @tbl TABLE(ID INT, [year] INT, reading INT, writing INT, spelling INT);
INSERT INTO @tbl VALUES
     (33087,7,625,620,686)
    ,(33087,8,544,560,541)
    ,(33205,7,559,572,497)
    ,(33205,8,599,560,612)
    ,(33902,7,500,484,464)
    ,(33902,8,607,560,686);

--the query:

SELECT t.ID
      ,MAX(CASE WHEN t.[year]=7 THEN t.[year] END) AS year_1
      ,MAX(CASE WHEN t.[year]=7 THEN t.reading END) AS reading_1
      ,MAX(CASE WHEN t.[year]=7 THEN t.writing END) AS writing_1
      ,MAX(CASE WHEN t.[year]=7 THEN t.spelling END) AS spelling_1

      ,MAX(CASE WHEN t.[year]=8 THEN t.[year] END) AS year_2
      ,MAX(CASE WHEN t.[year]=8 THEN t.reading END) AS reading_2
      ,MAX(CASE WHEN t.[year]=8 THEN t.writing END) AS writing_2
      ,MAX(CASE WHEN t.[year]=8 THEN t.spelling END) AS spellingg_

      --add more blocks if needed
FROM @tbl t
GROUP BY t.ID;

PIVOT is limited to one single column. In your case you want to "pivot" several columns at once. This approach offers most control over the way you compute/aggregate your data, if there is more than one row per ID and year.
Another advantage: This is quite easy to create dynamically. In this case you would have to look into your data, find the occuring years and create one block per year. Then you execute the statement.

Another approach was conditional joining:

WITH IDsOnly AS
(
SELECT t.ID
FROM @tbl t
GROUP BY t.ID
)
SELECT IDsOnly.ID
      ,A.[year] AS year_1,A.reading AS reading_1,A.writing AS writing_1,A.spelling AS spelling_1
      ,B.[year] AS year_2,B.reading AS reading_2,B.writing AS writing_2,B.spelling AS spelling_2
FROM IDsOnly
LEFT JOIN(SELECT * FROM @tbl t7 WHERE t7.[year]=7) AS A ON IDsOnly.ID=A.ID
LEFT JOIN(SELECT * FROM @tbl t7 WHERE t7.[year]=8) AS B ON IDsOnly.ID=B.ID;
--Add more joins if needed...

score 0 · Accepted Answer · answered May 15 '19 at 07:46

Using self join we can get the result, Try This

;WITH CTE(ID ,[year],reading, writing, spelling)
AS
(
SELECT 33087,7,625,620,686 UNION ALL
SELECT 33087,8,544,560,541 UNION ALL
SELECT 33205,7,559,572,497 UNION ALL
SELECT 33205,8,599,560,612 UNION ALL
SELECT 33902,7,500,484,464 UNION ALL
SELECT 33902,8,607,560,686      
)
SELECT i.ID ,i.[year],i.reading, i.writing, i.spelling,
            o.[year],o.reading, o.writing, o.spelling
FROM CTE i
INNER JOIN CTE o 
    ON i.ID = o.ID
WHERE i.[year] =7 
    AND o.[year] =8

Result

ID      year    reading writing spelling    year    reading writing spelling
-----------------------------------------------------------------------------
33087   7         625    620      686         8       544      560    541
33205   7         559    572      497         8       599      560    612
33902   7         500    484      464         8       607      560    686

user11493159 · Answer 3 · 2019-05-15T22:15:52.703

This code will arrange the data for each ID into a single line. The columns will be ordered by year and each year will be listed underneath each other for every ID. If a year is empty for a ID, it will be filled with NULL values. Only the years which occurs in the data set, will be listed in the resulting columns.

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @pc AS NVARCHAR(20)
DECLARE @column_list AS NVARCHAR(MAX) 

SELECT  @pc = CONVERT(NVARCHAR, PIVOT_CODE),
        @column_list = COALESCE(@column_list + ',', '') + ' year' + @pc + ', reading' + @pc + ', writing' +@pc + ', spelling' + @pc
FROM (    
    SELECT DISTINCT Year AS PIVOT_CODE FROM YourTable 
) AS DistYears
ORDER BY PIVOT_CODE

SET @sql = '
;WITH p AS (
    SELECT ID,''year'' + CAST(year AS NVARCHAR(10)) AS Col, year AS Val FROM YourTable
    UNION ALL
    SELECT ID,''reading'' + CAST(year AS NVARCHAR(10)) AS Col, reading AS Val FROM YourTable
    UNION ALL
    SELECT ID,''writing'' + CAST(year AS NVARCHAR(10)) AS Col, writing AS Val FROM YourTable
    UNION ALL
    SELECT ID,''spelling'' + CAST(year AS NVARCHAR(10)) AS Col, spelling AS Val FROM YourTable
)
SELECT ID, ' + @column_list + '
FROM p
PIVOT (
    MAX(Val)
    FOR Col IN (
        ' + @column_list + '
    )
) AS pvt
ORDER BY ID'

EXEC(@sql)

Convert rows into N columns in SQL Server

3 Answers3