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...
