locked
How to do a 'Case When' and 'Pivot' in the same query? RRS feed

  • Question

  • Hi,

    I have two question:

    I have the following query, but need some help,

    First: I want to do a "case When" for Column2, but it wont allow me, I believe it's because it's in the SUM(Column2) format; how can I go about applying a case when to that column?

    Secondly, for column1, there are a total of 4000 rows of information, (in the query below I tryped up to 20, but obviously that is not feasible to do for 4000). How can I specify in this query to retrieve all the 4000 rows of data.

    I'd appreciate any help you may provide me with.

    Thanks

     

    SELECT *

    FROM

    (

    SELECT

    PP.ColumnA, PP.ColumnB, CASE WHEN pp.ColumnC = 0 THEN 'Female' ELSE 'Male' END AS ColumnC, PP.ColumnD, PQA.Column1, PQA.Column2

    FROM Parents PP

    JOIN

    ParentsQuestionAnswers PQA

    ON

    PP.ColumnID = PQA.ColumnID

     DataTable

    PIVOT

    (

    SUM(Column2

    FOR

    Column1

    IN

    [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]

    PivotTable

    ORDER

    BY ColumnA

     

    )

    )

    • Edited by Pearln Friday, September 2, 2011 7:50 PM
    Friday, September 2, 2011 7:47 PM

Answers

  • Yep, if the column is participate in aggregation or FOR in PIVOT clause it cannot be used in CASE expression

    CREATE TABLE Foo (

      foo_type CHAR(1) PRIMARY KEY,

      foo_value INT,

      col INT );

     

    INSERT INTO Foo VALUES('A', 1,100);

    INSERT INTO Foo VALUES('B', 2,200);

    INSERT INTO Foo VALUES('C', 3,300);

    INSERT INTO Foo VALUES('D', 4,400);

     

     

     

    -- pivot using PIVOT operator (SQL Server 2005/2008)

    SELECT CASE WHEN foo_type ='A' THEN 1 ELSE 0 END col, A, B, C, D

    FROM Foo

    PIVOT

    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    --Msg 207, Level 16, State 1, Line 1

    --Invalid column name 'foo_type'.

     

    SELECT CASE WHEN col =100 THEN 1 ELSE 0 END col, A, B, C, D

    FROM Foo

    PIVOT

    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    ---works fine

     

    PS. In the second query in order to get desired result  you will need to use sub query to aggregate  the data

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 4, 2011 5:35 AM
  • I don't think you can create a resultset with more than 1024 columns. In any case, the dynamic PIVOT may be an option.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 6, 2011 5:03 PM
    Answerer
  • The way to debug such problems is to use PRINT @Query instead of EXECUTE (@Query).

    If you post your problem with DDL, insert statements and desired output, we should be able to help you more.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 6, 2011 8:28 PM
    Answerer

All replies

  • Yep, if the column is participate in aggregation or FOR in PIVOT clause it cannot be used in CASE expression

    CREATE TABLE Foo (

      foo_type CHAR(1) PRIMARY KEY,

      foo_value INT,

      col INT );

     

    INSERT INTO Foo VALUES('A', 1,100);

    INSERT INTO Foo VALUES('B', 2,200);

    INSERT INTO Foo VALUES('C', 3,300);

    INSERT INTO Foo VALUES('D', 4,400);

     

     

     

    -- pivot using PIVOT operator (SQL Server 2005/2008)

    SELECT CASE WHEN foo_type ='A' THEN 1 ELSE 0 END col, A, B, C, D

    FROM Foo

    PIVOT

    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    --Msg 207, Level 16, State 1, Line 1

    --Invalid column name 'foo_type'.

     

    SELECT CASE WHEN col =100 THEN 1 ELSE 0 END col, A, B, C, D

    FROM Foo

    PIVOT

    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    ---works fine

     

    PS. In the second query in order to get desired result  you will need to use sub query to aggregate  the data

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 4, 2011 5:35 AM
  • Thanks Uri for the help.

    I did exactly that, and changed the 'Sum' to 'Max' and the 'Case when' works, however I'm still having problems with the second part, and can't figure out how to get all the columns...

    Is Dynamic Pivoting the option?

    Thanks

    Tuesday, September 6, 2011 4:47 PM
  • I don't think you can create a resultset with more than 1024 columns. In any case, the dynamic PIVOT may be an option.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 6, 2011 5:03 PM
    Answerer
  • Ok, this is what I have, but it gives me Syntax error "Incorrect Syntax near 'female'

    Is this even a correct query to get all the rows or not?

    Thanks

     

    DECLARE

    @Columns VARCHAR(MAX

    )

    SELECT

    @Columns = COALESCE(@Columns + ',[' + CAST(Column1 as varchar) + ']'

    ,

     

    '[' + cast(Column1 as Varchar)+ ']')
     

    FROM

    ParentsQuestionAnswers

    GROUP

    BY Column1 DECLARE @Query VARCHAR(MAX

    )

    SET

    @Query=

    '

    SELECT *

    FROM

    (

    SELECT PP.ColumnA, PP.ColumnB, CASE WHEN pp.ColumnC = 0 THEN '

    Female' ELSE 'Male' END AS ColumnC, PP.ColumnD, PQA.Column1, CASE WHEN PQA.Column2 = 1 THEN 'Yes' Else 'No' END AS Column2

    FROM Parents PP

    JOIN ParentsQuestionAnswers PQA

    ON PP.ColumnID = PQA.ColumnID

    ) DataTable

    PIVOT

    (MAX(Column2)

    FOR Column1 IN (

    '

    +@columns +

    )

    )

    )

    PivotTable

    ORDER

    BY ColumnA

    Tuesday, September 6, 2011 6:49 PM
  • Correct it as  ....THEN '''Female''' ELSE '''Male''' END AS Colu.....

    Tuesday, September 6, 2011 6:57 PM
  • Thank you so much.

    It now gives me syntax error for this: " Incorrect syntax near ')' "

    Even when I remove or replace the parenthesis, it still gives error!

     

    ... IN

     (

    '

    +@columns +

    )

    )

    )

    PivotTable

    ORDER

    BY ColumnA

    Tuesday, September 6, 2011 7:07 PM
  • I think it should be

    (

    '

     

     

     +@columns +

     

    PivotTable

    ORDER

     BY ColumnA

     

    ')

    )

    )'

     

    Tuesday, September 6, 2011 7:21 PM
  • No Luck!
    Tuesday, September 6, 2011 8:15 PM
  • The way to debug such problems is to use PRINT @Query instead of EXECUTE (@Query).

    If you post your problem with DDL, insert statements and desired output, we should be able to help you more.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 6, 2011 8:28 PM
    Answerer