Dynamic Pivot in SQL Server

USE tempdb

GO

CREATE TABLE RESULT

( SID INT,

SUB VARCHAR(10),

MARKS INT

)

GO

INSERT INTO RESULT

SELECT 1,'SUB1',54 UNION ALL SELECT 1,'SUB2',46 UNION ALL SELECT 1,'SUB3',57 UNION ALL SELECT 1,'SUB4',86 UNION ALL

SELECT 2,'SUB1',64 UNION ALL SELECT 2,'SUB3',34 UNION ALL SELECT 2,'SUB5',76 UNION ALL SELECT 2,'SUB6',96 UNION ALL

SELECT 3,'SUB2',54 UNION ALL SELECT 3,'SUB4',46 UNION ALL SELECT 3,'SUB5',57 UNION ALL SELECT 3,'SUB6',86 UNION ALL

SELECT 4,'SUB1',64 UNION ALL SELECT 4,'SUB3',34 UNION ALL SELECT 4,'SUB4',76 UNION ALL SELECT 4,'SUB6',96

GO

SELECT * FROM RESULT

GO

-- NORMAL PIVOT TABLE --

SELECT * FROM RESULT

PIVOT (MAX(MARKS) FOR SUB

IN (SUB1,SUB2,SUB3,SUB4,SUB5,SUB6))AS PVT

WHERE SID = 1

SID SUB1 SUB2 SUB3 SUB4 SUB5 SUB6

1 54 46 57 86 NULL NULL

-- DYNAMIC PIVOT TABLE USING STUFF()—-

DECLARE @SID VARCHAR(8)

DECLARE @QRY VARCHAR(MAX)

SET @SID = '1'

SET @QRY = (SELECT DISTINCT STUFF(( SELECT ', ' + CAST(SUB AS VARCHAR(MAX)) FROM RESULT WHERE (SID = @SID)

                  FOR XML PATH ('')),1,2,'') AS SUB

                  FROM RESULT)

SELECT @QRY = 'SELECT * FROM

                  (

                  SELECT * FROM RESULT WHERE SID = '+@SID+'

                  ) V1

                  PIVOT (MAX(MARKS) FOR SUB IN

                  ('+@QRY+')

                  )AS PVT'

EXECUTE(@QRY)

SID SUB1 SUB2 SUB3 SUB4

1 54 46 57 86

-- DYNAMIC PIVOT USING COALESCE() --

DECLARE @SID VARCHAR(8)

DECLARE @QRY1 VARCHAR(MAX)

SET @SID = '1'

SELECT @QRY1 = COALESCE(@QRY1 +',','') + SUB FROM

                  (SELECT SUB FROM RESULT WHERE SID = @SID) AS X

                 

SELECT @QRY1 = 'SELECT * FROM

                  (

                  SELECT * FROM RESULT WHERE SID = '+ @SID +'

                  ) V1

                  PIVOT (MAX(MARKS) FOR SUB IN

                  ('+ @QRY1 +')

                  )AS PVT'

                 

EXECUTE(@QRY1)

SID SUB1 SUB2 SUB3 SUB4

1 54 46 57 86

DROP TABLE RESULT

Hope, you are understand. Thank You...