June 23, 2015 at 7:37 am
Hi,
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis. Please suggest idea.
select (
Column1
,Column2
,Column3
,Column4
,coloumn5
from Mytable
) x
pivot
(
case when Column1 = 6 then sum(Column3) else max(Column4) End
for coloumn5 in (' + @COLS + ')
)p
Thanks,
Abhas.
June 23, 2015 at 8:41 am
That part of the PIVOT is only expecting an Aggregate Function.
If you look up CASE: https://msdn.microsoft.com/en-us/library/ms181765.aspx
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
June 23, 2015 at 9:15 am
abhas (6/23/2015)
Hi,Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis. Please suggest idea.
select (
Column1
,Column2
,Column3
,Column4
,coloumn5
from Mytable
) x
pivot
(
case when Column1 = 6 then sum(Column3) else max(Column4) End
for coloumn5 in (' + @COLS + ')
)p
Thanks,
Abhas.
Just saying there is an error is like taking your car to the mechanic and saying it needs to be fixed. You need to provide information.
I can tell you though that your case expression will never work like that. A case expression is not used like that. It returns a column, not a logical operator like you are you trying to do.
It seems from your snippet that a cross tab would be a better approach here instead of a PIVOT. Take a look at the links in my signature. If you have problems, please take a look at the first link in my signature and post us some details. We will be happy to help if you post information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply