CODEWITHSUNDEEP
sqlsql-serversql-server-2008
RAP
RAP

Reputation: 83

SQl Server Pivot Query (simple group by?)

How can I do a pivot on the following result set in SQL Server:

Source      Type    Count   Sum
--------------------------------------
Apple       FALSE   67      153981
Egg         FALSE   115     75821
Egg         TRUE    832     112773
Jam         FALSE   81      71758
Jam         TRUE    648     85784
Other       FALSE   1228    521206
Other       TRUE    2       282
Pineapple   FALSE   521     1292074
Pineapple   TRUE    698     146585
Watermelon  FALSE   1146    1815607
Watermelon  TRUE    4157    761837

My end result should look like this. I think this can't be done by simple group by, if I'm correct ?

Source      TRUE    FALSE   Sum
--------------------------------------
Apple       0       67      153981
Egg         832     115     188594
Jam         648     81      157542
Other       2       1228    521488
Pineapple   698     521     1438659
Watermelon  1146    4175    2577444

Appreciate your help!

Upvotes: 0

Views: 77

Answers (2)

mohan111
mohan111

Reputation: 8865

may be this other way to answer for same result set by using PIVOT 

DECLARE @Table1 TABLE 
    (Source varchar(10), Type varchar(5), Count int, Sum int)
;

INSERT INTO @Table1
    (Source, Type, Count, Sum)
VALUES
    ('Apple', 'FALSE', 67, 153981),
    ('Egg', 'FALSE', 115, 75821),
    ('Egg', 'TRUE', 832, 112773),
    ('Jam', 'FALSE', 81, 71758),
    ('Jam', 'TRUE', 648, 85784),
    ('Other', 'FALSE', 1228, 521206),
    ('Other', 'TRUE', 2, 282),
    ('Pineapple', 'FALSE', 521, 1292074),
    ('Pineapple', 'TRUE', 698, 146585),
    ('Watermelon', 'FALSE', 1146, 1815607),
    ('Watermelon', 'TRUE', 4157, 761837)
;
Select  Source,
        ISNULL(MAX([TRUE]),0)[TRUE],
        ISNULL(MAX([FALSE]),0)[FALSE],
        ISNULL(MAX(SUM),0)SUM 
FROM (
Select Source,Sum,[TRUE],[FALSE]    
    from (
select Source, Sum,Count,Type 
    from @Table1)T
PIVOT (MAX(Count) FOR   
        Type IN([TRUE],[FALSE]) )P)TT
GROUP BY TT.Source

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this using conditional aggregation:

SELECT
    [Source],
    [True]  = SUM(CASE WHEN [Type] = 'TRUE' THEN [Count] ELSE 0 END),
    [False] = SUM(CASE WHEN [Type] = 'FALSE' THEN [Count] ELSE 0 END),
    [Sum]   = SUM([Sum])
FROM tbl
GROUP BY [Source]

As a side note, you should avoid using reserved words such as COUNT and SUM as your column names.

Upvotes: 1

Related Questions