Transpose / Dynamic PIVOT

enter image description here

into the following:

enter image description here

Credit is limited with 6. Blank spots can be null. I want to create new columns named as Credit1,Credit2, .. ,Credit6. Can I use PIVOT for this scenario? Or any other simple way to transpose the table?

Upvotes: 0

Views: 199

Answers (1)

TriV
TriV

Reputation: 5148

You could use dynamic SQL version:

CREATE TABLE  #SampleData 
(
   Name varchar(50),
   Credits int
)

INSERT INTO #SampleData
VALUES ('John', 10),('John', 20),
('Bath', 90), ('Bath', 60), ('Bath', 70),('Bath', 80),('Bath', 50),
('Richard', 75)

DECLARE @ColPivot nvarchar(max)
;WITH temp AS
(
   SELECT * , 'Credit' + CAST(row_number() OVER(PARTITION BY sd.Name ORDER BY (SELECT 1)) AS varchar(5)) AS CreditGroup
   FROM #SampleData sd
)
SELECT @ColPivot = STUFF(
                    (SELECT DISTINCT ',' + t.CreditGroup FROM temp t FOR XML PATH (''))
                   ,1,1,'')

DECLARE @query nvarchar(max) = 
      N';WITH temp AS
        (
            SELECT * , ''Credit'' + CAST(row_number() OVER(PARTITION BY sd.Name ORDER BY (SELECT 1)) AS varchar(5)) AS CreditGroup
            FROM #SampleData sd
        )
         Select [Name],  ' + @ColPivot + 
        ' FROM 
         (
            SELECT [Name], Credits, CreditGroup FROM temp 
         ) src
         PIVOT
         (
            MAX(Credits) FOR CreditGroup IN ('+ @ColPivot+ ')
         )pvt
        '
PRINT @query

EXEC (@query)

DROP TABLE #SampleData

Demo link: Rextester

Upvotes: 1

Related Questions