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')

And I would like to get this

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

CodePudding user response：

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

CodePudding user response：

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 @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 @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'   @s1   @s2   @s3   N'
FROM #s GROUP BY grp, id;';
          
PRINT @sql;
EXEC sys.sp_executesql @sql;

Print output:

SELECT grp, id,
       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;

Execution results:

grp	id	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	23.5	24	23	0	1	1	1	0	1	1	1	0
GR2	FVS1	23.5	24	23	0	10	10	10	0	10	10	10	0
GR1	VX1	0	25	24.5	25	0	100	30	10	0	95	20	95
GR1	VX2	0	20	19	0	0	10	5	0	0	10	5	0

• Example db<>fiddle