rows to columns based on first column

Question

I have a student table like below

name | subject  | scode
sam  | science  | 20
sam  | computer | 30
sam  | language | 50
sam  | history  | 20
joe  | PET      | 30
joe  | computer | 50
dan  | lab      | 40

i am looking for out put like below

name |  20      | 30       | 40    | 50 
sam  |  science | computer | null  | language 
sam  | history  | null     | null  | null
joe  |  null    | PET      | null  | Computer
dan  |  null    | null     | lab   | null

there are chances a student can add one more subject in future and code is dynamic for that particular student

i tried using for xml however able to get in the format of xml but not able to transpose it. any help in pivoting this as per the output is possible?

And what happens when sam has scode of 20 for more than 1 row? — Sean Lange, Mar 21 '18 at 20:42
@SeanLange a new row with sam for scode 20 has be made. sorry i missed in the requirement — usr5860, Mar 21 '18 at 20:44
OK. Does it matter that science is in the first row for sam and history is in the second row? I think the biggest issue here is your data seems to lack some normalization. — Sean Lange, Mar 21 '18 at 20:47
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Adrian, Mar 22 '18 at 07:01

Tyron78 · Accepted Answer · 2018-03-26T10:50:35.847

I think pivoting in combination with dynamix SQL qould do the trick. I created an approach for this, but this will require some more modification: currently it would group away the second line with scode 20 for student sam. Give it a try - when you get stuck, I will try to modify it a little more:

IF OBJECT_ID('dbo.tbl_test') IS NOT NULL
  DROP TABLE tbl_test
GO

CREATE TABLE tbl_test (
  sName varchar(25)
 ,sSubject varchar(25)
 ,sCode int
)
GO

INSERT INTO tbl_test VALUES
 ('sam', 'science', 20)
,('sam', 'computer', 30)
,('sam', 'language', 50)
,('sam', 'history', 20)
,('joe', 'PET', 30)
,('joe', 'computer', 50)
,('dan', 'lab', 40)


DECLARE @Cols NVARCHAR(MAX);
DECLARE @Qry NVARCHAR(MAX);

SELECT @Cols = STUFF((SELECT DISTINCT ', [' + CAST(scode AS VARCHAR(5)) + ']'
  FROM tbl_test
  ORDER BY 1
  FOR XML PATH ('')), 1, 1, '')

SET @Qry = 'WITH cte AS(
SELECT sName_GRP, sName, ' + @Cols + '
FROM (
  SELECT sName, sCode, sSubject, sName + ' + CHAR(39) + '_' + CHAR(39) + ' + RIGHT(' + char(39) + '0000' + CHAR(39) +' + CAST(ROW_NUMBER() OVER (PARTITION BY sName, sCode ORDER BY sName, sCode) AS VARCHAR(5)), 5) sName_GRP
    FROM tbl_test
) AS j
PIVOT
(
  MAX(sSubject) FOR sCode in (' + @Cols + ')
) AS p
)
SELECT sName, ' + @Cols + '
  FROM cte'

EXEC sp_executesql @Qry

thanks tyron this works in grouping but second line for sam 20 is not appearing. i believe it is because we are using the aggregate in pivot which takes only the max value please advice — usr5860, Mar 22 '18 at 18:35
Please see modified Code. Duplicate sCode is considered now. — Tyron78, Mar 26 '18 at 10:50
@usr5860 so, did you have any chance to test the modified code yet? — Tyron78, Mar 27 '18 at 14:16
this worked correctly i will test further and update you thanks for your help — usr5860, Mar 28 '18 at 18:35

rows to columns based on first column

1 Answers1