1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Dynamic PIVOT query
-- Column names are dynamically generated
-- Use of sp_executeSql to execute the SQL query.
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @ColumnNames AS NVARCHAR(MAX)
CREATE TABLE #Exposure (Category varchar(30), Currency varchar(30), Amount money)
INSERT INTO #Exposure VALUES ('Liabilities','USD',42096)
,('Liabilities','JPY',145600)
,('Liabilities','EUR',4620000)
,('Liabilities','CHF',2400)
,('Liabilities','NOK',2100)
,('Liabilities','AUD',500)
,('Liabilities','NZD',718)
,('Swaps','USD',31501)
,('Swaps','JPY',8000)
,('Swaps','EUR',4120)
,('Swaps','CHF',-1750)
,('Swaps','NOK',-2100)
,('Swaps','AUD',-500)
,('Swaps','NZD',-718)
,('FX','USD',-10589)
,('FX','JPY',-98000)
,('FX','EUR',-500)
,('FX','CHF',-200)
,('FX','NOK',0)
,('FX','AUD',0)
,('FX','NZD',0)
--Get distinct values of the PIVOT Column
SELECT @ColumnNames= ISNULL(@ColumnNames + ',','')
+ QUOTENAME(Currency)
FROM (SELECT DISTINCT Currency FROM #Exposure) AS Currencies
SET @SQL =
'SELECT Category, ' + @ColumnNames + '
FROM (
SELECT Category, Currency, Amount
FROM #Exposure) e
PIVOT (SUM(Amount)
FOR Currency IN (' + @ColumnNames + ')) AS pvt'
EXEC sp_executeSql @SQL
DROP TABLE #Exposure
Dynamic PIVOT query
This post is licensed under CC BY 4.0 by the author.