use case statement in PIVOT

  • 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.

  • 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.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • 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