Results 1 to 9 of 9

Thread: PIVOT query

  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: PIVOT query

    Need help with PIVOT query. All outputs become zeros

    Table look like this:
    [c1] [varchar](10) NULL
    [c2] [varchar](10) NULL
    [c3] [int] NULL
    --------------------------------
    Contains this:
    c1 c2 c3
    KATE MILK 5
    KATE BEER 5
    KATE MILK 5
    KATE BEER 5
    HANNAH MILK 6
    HANNAH BEER 6
    HANNAH MILK 6
    HANNAH BEER 6
    HANNAH MILK 6
    --------------------------------
    The query:
    SELECT C1,
    [R1],
    [R2],
    [R3]
    FROM
    ( SELECT C1,C2,C3
    FROM __lab) A
    PIVOT ( COUNT(C2)
    FOR C2 IN ( [R1],[R2],[R3] )
    ) B
    --------------------------------
    Gives this:
    C1 R1 R2 R3
    KATE 0 0 0
    KATE 0 0 0
    HANNAH 0 0 0
    KATE 0 0 0
    HANNAH 0 0 0
    KATE 0 0 0
    --------------------------------
    But what im trying to get is this: (example numbers only)
    HANNAH KATE
    BEER 30 20
    MILK 20 30

    I want the output to show how many times HANNAH and KATE have BEER vs MILK in all rows. Just ignore c3
    How can i get the output above with PIVOT function?

    Thanks in advance

    //Mr

  2. #2
    Join Date
    Feb 2012
    Posts
    7
    You'll need to flip over the pivot
    so you've got...

    Code:
    SELECT C1,
    [R1],
    [R2],
    [R3]
    FROM 
    ( SELECT C1,C2,C3
    FROM __lab) A
    PIVOT ( COUNT(C2) 
    FOR C2 IN ( [R1],[R2],[R3] ) 
    ) B
    Lets change it to...

    Code:
    SELECT C2, [MILK], [BEER]
    FROM (SELECT C2,C1,C3
    FROM __lab) A
    PIVOT ( COUNT(C2) 
    FOR C1 IN ( [MILK], [BEER]) 
    ) B
    So C2 (Drink) is now your first column, pivot columns are now populated from C1 (name).

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    thanks for your replay, unfournately it dosen't work.
    It seems like the C2 is an invalid column in "SELECT C2, [MILK], [BEER]"

    If i change the "PIVOT ( COUNT(c2)" to "PIVOT ( COUNT(c3)" it works but still give me zeros

  4. #4
    Join Date
    Feb 2012
    Posts
    7
    Try

    Code:
    SELECT *
    FROM (SELECT C2,C1,C3
    FROM __lab) A
    
    PIVOT 
    
    ( SUM(C2) 
    FOR C1 IN ( [MILK], [BEER]) 
    ) AS B

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    sorry, can't SUM varchar columns.

    "Operand data type varchar is invalid for sum operator."

  6. #6
    Join Date
    Feb 2012
    Posts
    7
    sum(c3) then

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    yes that works but that's not the result i want.
    I want the query to count how many times "milk" and "beer" there is. Not the sum of the c3

    thanks for your effort

  8. #8
    Join Date
    Feb 2012
    Posts
    7
    You've asked for
    HANNAH KATE
    BEER 30 20
    MILK 20 30


    What does my query return?

    If you do a count(c3) I assume you would get

    C1 HANNAH KATE
    MILK 3 2
    BEER 3 2

    Wouldn't sum(c3) give you the result you are after?

  9. #9
    Join Date
    Mar 2004
    Posts
    162
    since C3 contains more then value of 1 (5 for Kate and 6 for Hannah) the sum would give me the summarize of the value for that column and not a count for each "MILK" and "BEER"

    table look like this:

    C1, C2, C3
    KATE, MILK, 5
    KATE, BEER, 5
    HANNAH, MILK, 6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •