I want to PIVOT my data on TradeMonth. below is how my data looks.
Lets call it table test_pivot
TradeMonth risk_hd risk_val
201808 abc 3.20364
201809 abc 3.155
201810 abc 3.58318
201811 abc 3.82526
201808 def 123191019
201809 def 107184352.9
201810 def 99110873.05
201811 def 104231257.9
If I pivot on risk_hd, I get the results however query is not working if I pivot on column TradeMonth. below is the query I am trying. I looked all around but nothing worked . Getting Syntax error near '201819' and near 'as'.
select P.risk_hd, P.[201809] , P.[201810]
from
(select * from Test_Pivot) as S
Pivot
(
sum(risk_val)
FOR TradeMonth IN ([201811], [201812] )
)as P
Unless it is a typo in your post rather than in your code, you are pivoting on 201811 and 201812 (which then would be the columns in the output), but are trying to sect non-existent columns 201809 and 201810.
I would think the query should be something like this:
SELECT P.risk_hd,
P.[201808],
P.[201809],
P.[201810],
[201811]
FROM
(SELECT * FROM Test_Pivot) AS S
PIVOT
(
SUM(risk_val)
FOR TradeMonth IN ([201808], [201809],[201810],[201811])
) AS P;
Not sure what you are looking for, but this will pivot on TradeMonth
drop table if exists #TestPivot
Create table #testPivot
(TradeMonth int,
risk_hd varchar(10),
risk_val numeric (12,2))
insert into #TestPivot values
(201808,'abc','3.20364'),
(201809,'abc','3.155'),
(201810,'abc','3.58318'),
(201811,'abc','3.82526'),
(201808,'def','123191019'),
(201809,'def','107184352.9'),
(201810,'def','99110873.05'),
(201811,'def','104231257.9')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.TradeMonth)
FROM #TestPivot c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT risk_hd, ' + @cols + ' from
(
select risk_hd
, TradeMonth
,risk_Val
from #TestPivot
) x
pivot
(
sum(risk_val)
for TradeMonth in (' + @cols + ')
) p '
execute(@query)