CODEWITHSUNDEEP
sqlsql-server
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Case statements to appear on the same row SQL Server

Im having real difficulty with a query.

The current query is 

SELECT 
min(ID),
Date, 
CASE WHEN (ID) = '111' then SUM(Column1) END AS [Outcome1],
CASE WHEN (ID) = '222' then SUM(Column1) END AS [Outcome2],
CASE WHEN (ID) = '333' then SUM(Column1) END AS [Outcome3],
CASE WHEN (ID) = '444' then SUM(Column1) END AS [Outcome4]
FROM Table1
GROUP BY ID, Date

And the result produces 

|  ID |     Date | Outcome1 | Outcome2 | Outcome3 | Outcome4 |
|-----|----------|----------|----------|----------|----------|
| 111 | 01/01/18 |       20 |   (null) |   (null) |   (null) |
| 222 | 01/01/18 |   (null) |       20 |   (null) |   (null) |
| 333 | 01/01/18 |   (null) |   (null) |       20 |   (null) |
| 444 | 01/01/18 |   (null) |   (null) |   (null) |       20 |

However the result i am looking for is 

|  ID |     Date | Outcome1 | Outcome2 | Outcome3 | Outcome4 |
|-----|----------|----------|----------|----------|----------|
| 111 | 01/01/18 |       20 |       20 |       20 |       20 |

I have tried grouping but i end up with a result with just outcome1 at 80. But i am looking for it to be displayed with the 4 outcome columns.

Can anyone help me with this? Thanks

SQL Fiddle - http://sqlfiddle.com/#!18/9516c/2

Upvotes: 1

Views: 631

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Apply SUM aggregate on top of case statement

SELECT 
min(ID),
Date, 
SUM(CASE WHEN (ID) = '111' then Column1 END) AS [Outcome1],
SUM(CASE WHEN (ID) = '222' then Column1 END) AS [Outcome2],
SUM(CASE WHEN (ID) = '333' then Column1 END) AS [Outcome3],
SUM(CASE WHEN (ID) = '444' then Column1 END) AS [Outcome4]
FROM Table1
GROUP BY Date

Also remove the ID from group by

Upvotes: 4

Related Questions