Following is the sample table. need to pivot a column (Commission) and count of deptids
CREATE TABLE #Table1 (Region varchar(10),DeptId INT,Commission varchar(10),comm_attribute1 decimal,comm_attribute2 decimal,comm_attribute3 decimal);
INSERT INTO #Table1 VALUES('ABC',1,'HR',10,20,30);
INSERT INTO #Table1 VALUES('ABC',3,'HR',10,20,30);
INSERT INTO #Table1 VALUES('ABC',7,'DEV',10,20,30);
INSERT INTO #Table1 VALUES('ABC',4,'DEV',10,20,30);
INSERT INTO #Table1 VALUES('ABC',5,'HR',10,20,30);
INSERT INTO #Table1 VALUES('ABC',8,'HR',10,20,30);
Following is the query but failed to pivot
WITH T AS
(
SELECT Region, deptCOUNT, Commission, att1 + att2 + att3 AS total
FROM (SELECT Region,
Commission,
COUNT(Region) as deptCOUNT,
SUM(comm_attribute1) AS att1,
SUM(comm_attribute2) AS att2,
SUM(comm_attribute3) AS att3
FROM #Table1
GROUP BY Region, Commission) tmp
)
SELECT *
FROM T
PIVOT (
MAX(total) FOR Commission IN([ DEV ], [ HR ])
) P
Need following result
| Region | DeptCount | Dev | HR |
|---|---|---|---|
| ABC | 6 | 120 | 24 |
| XYZ | 2 | 100 | 200 |
