By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #tempIE (AGGREGATIONNAME varchar(2),
LTD decimal,
BOY decimal,
MONTH int);
INSERT INTO #tempIE
VALUES ('XX', 50, 45, 00),
('XX', 150, 145, 01),
('XX', 300, 295, 02),
('YY', 25, 20, 00),
('YY', 50, 45, 01),
('YY', 75, 70, 02),
('ZZ', 500, 495, 00),
('ZZ', 600, 595, 01),
('ZZ', 700, 695, 02);
9 rows affected
SELECT IE.[MONTH],
MAX(CASE IE.AGGREGATIONNAME WHEN 'XX' THEN IE.LTD END) AS XX_LTD,
MAX(CASE IE.AGGREGATIONNAME WHEN 'YY' THEN IE.LTD END) AS YY_LTD,
/* ... */
MAX(CASE IE.AGGREGATIONNAME WHEN 'ZZ' THEN IE.BOY END) AS ZZ_BPY
FROM #tempIE IE
GROUP BY IE.[MONTH];
| MONTH | XX_LTD | YY_LTD | ZZ_BPY |
|---|---|---|---|
| 0 | 50 | 25 | 495 |
| 1 | 150 | 50 | 595 |
| 2 | 300 | 75 | 695 |
Warning: Null value is eliminated by an aggregate or other SET operation.
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delim nvarchar(3) = N',' + @CRLF
SELECT @SQL = N'SELECT IE.[MONTH],' + @CRLF +
STRING_AGG(N' MAX(CASE IE.AGGREGATIONNAME WHEN ' + QUOTENAME(IE.AGGREGATIONNAME,'''') + N' THEN IE.LTD END) AS ' + QUOTENAME(CONCAT(IE.AGGREGATIONNAME,'_LTD')),@Delim) WITHIN GROUP (ORDER BY IE.AGGREGATIONNAME) + N',' + @CRLF +
STRING_AGG(N' MAX(CASE IE.AGGREGATIONNAME WHEN ' + QUOTENAME(IE.AGGREGATIONNAME,'''') + N' THEN IE.BOY END) AS ' + QUOTENAME(CONCAT(IE.AGGREGATIONNAME,'_BOY')),@Delim) WITHIN GROUP (ORDER BY IE.AGGREGATIONNAME) + @CRLF +
N'FROM #tempIE IE' + @CRLF +
N'GROUP BY IE.[MONTH];'
FROM (SELECT DISTINCT AGGREGATIONNAME
FROM #tempIE) IE;
--PRINT @SQL;--Your best friend
EXEC sys.sp_executesql @SQL;
| MONTH | XX_LTD | YY_LTD | ZZ_LTD | XX_BOY | YY_BOY | ZZ_BOY |
|---|---|---|---|---|---|---|
| 0 | 50 | 25 | 500 | 45 | 20 | 495 |
| 1 | 150 | 50 | 600 | 145 | 45 | 595 |
| 2 | 300 | 75 | 700 | 295 | 70 | 695 |
Warning: Null value is eliminated by an aggregate or other SET operation.
DROP TABLE #tempIE;