asked on

pivot() function

What's wrong with this query?

select Type, MonthNo, amount from MyTable
pivot (
sum(amount)
for MonthNo
in (3, 4, 5, 6)
) as p

it returns error: Incorrect syntax near '3'.
I tried putting it also on quotation marks as : in ('3', '4', '5', '6'). but same error :(

Type is in varchar
MonthNo is an integer (values are 3, 4, 5, and 6)
Amount is of money data type

appari

try

select Type, MonthNo, amount from MyTable
pivot (
sum(amount)
for MonthNo
in ([3],[4], [5], [6])
) as p

appari

or

select [3],[4], [5], [6] from MyTable
pivot (
sum(amount)
for MonthNo
in ([3],[4], [5], [6])
) as p

ptjcb

SELECT
Type,
MonthNo,
AmountMarch = ISNULL([3],0),
AmountApril = ISNULL([4],0),
AmountMay = ISNULL([5], 0),
AmountJune = ISNULL([6],0)
FROM
MyTable
PIVOT (
SUM(amount)
FOR MonthNo IN ([3].[4],[5],[6])
) AS p

Faiga Diegel

ASKER

Appari,

I used your second suggestion and it worked. However, i think I didnt get the correct result. It should be the sum of all Month No per type, right?

Type 3 4 5 6
car      0.00      NULL      NULL      NULL
car       NULL      NULL      30.00      NULL
car       NULL      NULL      -0.52      NULL
car       NULL      NULL      -0.22      NULL
house       NULL      NULL      0.15      NULL
house      NULL      NULL      0.15      NULL
house       NULL      NULL      3.97      NULL
house       NULL      NULL      6.96      NULL
house       NULL      NULL      25.00      NULL
car       NULL      14.63      NULL      NULL
car       NULL      -1.24      NULL      NULL
car       NULL      1.00      NULL      NULL
car       NULL      -0.15      NULL      NULL
house       NULL      0.10      NULL      NULL
house       NULL      1.00      NULL      NULL
house       NULL      1.08      NULL      NULL
house       NULL      1.20      NULL      NULL
house       NULL      -1.24      NULL      NULL
house       NULL      1.50      NULL      NULL
house       NULL      -14.63      NULL      NULL

ptjcb, it ried your suggestion but i got this error:
"Prefixed columns are not allowed in the column list of a PIVOT operator."

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Faiga Diegel

ASKER

Thanks appari... it worked with some modifications for NULL values. I took ptjcb idea of ISNULL command so I have to split points :)

thanks both of you! great help!