SQLTeam.com | Weblogs | Forums

SQL Server Pivot function

#1

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

Appreciate your help in advance. thanks!

0 Likes

#2

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;
0 Likes

#3

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)
0 Likes

#4

Thanks for the input. that's a typo. I just ran your query. getting same error message.

0 Likes

#5

Thanks Mike. This works like a charm!
Could you explain the code. What would be the value for @cols here. Didn't get that part.

0 Likes

#6

There is no 201819 anywhere in the code that I posted. So not clear how you can get that error message. Check for typos.

Is this part of a larger query? Is there '201819' somewhere else?

0 Likes

#7

the @Cols variable create columns out of all the distinct TradeMonths

0 Likes