CODEWITHSUNDEEP
sql-serverpivot
Manuel
Manuel

Reputation: 39

SQL Server Pivot on Multiple columns and dynamic column names

I am trying to pivot on multiple columns and have dynamic column names in the result. I am using SQL server 2014.

The original data looks like this

CREATE TABLE #s (grp varchar(3), id varchar(4), acc varchar(5), pr float, pos_live float,  pos_yest float, fnd varchar(2))

INSERT INTO #s Values ('GR1','VX1','CFD01',25,100,95,'KY')
INSERT INTO #s Values ('GR1','VX1','UCD01',24.5,30,20,'UC')
INSERT INTO #s Values ('GR1','VX1','US1',25,10,95,'US')
INSERT INTO #s Values ('GR1','VX2','CFD01',20,10,10,'KY')
INSERT INTO #s Values ('GR1','VX2','UCD01',19,5,5,'UC')
INSERT INTO #s Values ('GR1','FVS1','CFD01',24,1,1,'KY')
INSERT INTO #s Values ('GR1','FVS1','UCD01',23,1,1,'UC')
INSERT INTO #s Values ('GR1','FVS1','EU1',23.5,1,1,'EU')
INSERT INTO #s Values ('GR2','FVS1','CFD02',24,10,10,'KY')
INSERT INTO #s Values ('GR2','FVS1','UCD02',23,10,10,'UC')
INSERT INTO #s Values ('GR2','FVS1','EU2',23.5,10,10,'EU')

enter image description here

And I would like to get this

enter image description here

I am struggling to use the pivot function on multiple columns and additionaly display dynamic column names in the result.

Upvotes: 0

Views: 321

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

You don't really want to use PIVOT here explicitly because it is meant to pivot one column, not multiple. Conditional aggregation like in @D-Shih's answer is the way you want to go, except you can't do that if you expect the query to change with the data. So you can use dynamic SQL:

DECLARE @s0  nvarchar(max) = N'',
        @s1  nvarchar(max) = N'', 
        @s2  nvarchar(max) = N'', 
        @s3  nvarchar(max) = N'', 
        @sql nvarchar(max) = N'';

;WITH cols AS 
(
  SELECT fnd, efnd = char(39) + fnd + char(39) FROM #s
)
SELECT @s0 += N',
   acc_' + fnd + N'     '
            + N' = MAX(CASE fnd WHEN ' + efnd 
            + N' THEN acc      END)',
       @s1 += N',
   pr_' + fnd + N'      '
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pr       ELSE 0 END)',
       @s2 += N',
   pos_live_' + fnd 
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pos_live ELSE 0 END)',
       @s3 += N',
   pos_yest_' + fnd 
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pos_yest ELSE 0 END)'
FROM cols GROUP BY fnd, efnd;

SET @sql += N'SELECT grp, id' + @s0 + @s1 + @s2 + @s3 + N'
FROM #s GROUP BY grp, id ORDER BY grp;';
          
PRINT @sql;
EXEC sys.sp_executesql @sql;

Print output:

SELECT grp, id,
   acc_EU      = MAX(CASE fnd WHEN 'EU' THEN acc      END),
   acc_KY      = MAX(CASE fnd WHEN 'KY' THEN acc      END),
   acc_UC      = MAX(CASE fnd WHEN 'UC' THEN acc      END),
   acc_US      = MAX(CASE fnd WHEN 'US' THEN acc      END),
   pr_EU       = SUM(CASE fnd WHEN 'EU' THEN pr       ELSE 0 END),
   pr_KY       = SUM(CASE fnd WHEN 'KY' THEN pr       ELSE 0 END),
   pr_UC       = SUM(CASE fnd WHEN 'UC' THEN pr       ELSE 0 END),
   pr_US       = SUM(CASE fnd WHEN 'US' THEN pr       ELSE 0 END),
   pos_live_EU = SUM(CASE fnd WHEN 'EU' THEN pos_live ELSE 0 END),
   pos_live_KY = SUM(CASE fnd WHEN 'KY' THEN pos_live ELSE 0 END),
   pos_live_UC = SUM(CASE fnd WHEN 'UC' THEN pos_live ELSE 0 END),
   pos_live_US = SUM(CASE fnd WHEN 'US' THEN pos_live ELSE 0 END),
   pos_yest_EU = SUM(CASE fnd WHEN 'EU' THEN pos_yest ELSE 0 END),
   pos_yest_KY = SUM(CASE fnd WHEN 'KY' THEN pos_yest ELSE 0 END),
   pos_yest_UC = SUM(CASE fnd WHEN 'UC' THEN pos_yest ELSE 0 END),
   pos_yest_US = SUM(CASE fnd WHEN 'US' THEN pos_yest ELSE 0 END)
FROM #s GROUP BY grp, id ORDER BY grp;

Execution results:

grp id acc_EU acc_KY acc_UC acc_US pr_EU pr_KY pr_UC pr_US pos_live_EU pos_live_KY pos_live_UC pos_live_US pos_yest_EU pos_yest_KY pos_yest_UC pos_yest_US
GR1 FVS1 EU1 CFD01 UCD01 null 23.5 24 23 0 1 1 1 0 1 1 1 0
GR1 VX1 null CFD01 UCD01 US1 0 25 24.5 25 0 100 30 10 0 95 20 95
GR1 VX2 null CFD01 UCD01 null 0 20 19 0 0 10 5 0 0 10 5 0
GR2 FVS1 EU2 CFD02 UCD02 null 23.5 24 23 0 10 10 10 0 10 10 10 0

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

You can try to use condition aggregate function to make it, SUM with CASE WHEN

SELECT grp,
       id,
       SUM(CASE WHEN fnd = 'KY'THEN pr ELSE 0 END) pr_ky,
       SUM(CASE WHEN fnd = 'UC'THEN pr ELSE 0 END) pr_uc,
       SUM(CASE WHEN fnd = 'US'THEN pr ELSE 0 END) pr_us,
       SUM(CASE WHEN fnd = 'EU'THEN pr ELSE 0 END) pr_eu,
       SUM(CASE WHEN fnd = 'KY'THEN pos_live ELSE 0 END),
       SUM(CASE WHEN fnd = 'UC'THEN pos_live ELSE 0 END),
       SUM(CASE WHEN fnd = 'US'THEN pos_live ELSE 0 END),
       SUM(CASE WHEN fnd = 'EU'THEN pos_live ELSE 0 END),
       SUM(CASE WHEN fnd = 'KY'THEN pos_yest ELSE 0 END),
       SUM(CASE WHEN fnd = 'UC'THEN pos_yest ELSE 0 END),
       SUM(CASE WHEN fnd = 'US'THEN pos_yest ELSE 0 END),
       SUM(CASE WHEN fnd = 'EU'THEN pos_yest ELSE 0 END)
FROM #s
GROUP BY grp,id
ORDER BY grp

sqlfiddle

Upvotes: 3

Related Questions