BEGIN TRAN
--Create the table
CREATE TABLE #Pivot
(
ColA nvarchar(500),
ColB nvarchar(500),
ColC int
)
--Populate the data
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('A', 'X', 1)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('A', 'Y', 2)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('A', 'Z', 3)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('A', 'X', 4)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('A', 'Y', 5)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('B', 'Z', 6)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('B', 'X', 7)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('B', 'Y', 8)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('B', 'Z', 9)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('C', 'X', 10)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('C', 'Y', 11)
INSERT INTO #Pivot (ColA, ColB, ColC) VALUES('C', 'Z', 12)
--The data
SELECT * FROM #Pivot
--Group BY
SELECT
ColA,
ColB,
SUM(ColC)
FROM
#Pivot
GROUP BY
ColA,
ColB
--Manual PIVOT
SELECT
*
FROM
(
SELECT
ColA,
ColB,
ColC
FROM
#Pivot
) DATA
PIVOT
(
SUM(DATA.ColC)
FOR
ColB
IN
(
[X],[Y],[Z]
)
) PVT
--Dynamic PIVOT
DECLARE @columns nvarchar(max)
SELECT
@columns =
STUFF
(
(
SELECT DISTINCT
', [' + ColB + ']'
FROM
#Pivot
FOR XML PATH('')
), 1, 1, ''
)
EXEC
('
SELECT
*
FROM
(
SELECT
ColA,
ColB,
ColC
FROM
#Pivot
) DATA
PIVOT
(
SUM(DATA.ColC)
FOR
ColB
IN
(
' + @columns + '
)
) PVT
')
--The data again
SELECT * FROM #Pivot
ROLLBACK
No comments:
Post a Comment