conditonal field select

[jackiev]

How do I format a select statment that will retrieve fields from multiple records based on coditi0ns:
example:

Table contains:
ID , month , days off count

123 , jan , 15
123 , feb , 8
123 , mar , 3
456 , jan , 2
456 , mar , 5

I want my select result to show:
123, 15 , 8 , 3
456, 2 , (null) , 5

so, I am retrieving all the rows for ID 123 & combining the results to one selected record. The table has unique index ID/month.

Any ideas?

 

[markros]

You can use either PIVOT (SQL Server 2005 and up) or CASE based PIVOT

select ID, Jan, Feb,Mar from MyTable
PIVOT (sum(DaysOfCount) FOR [Month] IN ([Jan],[Feb],[Mar])) pvt

This works in SQL Server 2000

select ID, 
sum(case when [Month] = 'Jan' then DaysOffCount end) as Jan,
sum(case when [Month] = 'Feb' then DaysOffCount end) as Feb,
sum(case when [Month] = 'Mar' then DaysOffCount end) as Mar
from myTable group by ID

PluralSight Learning Library

 

[jackiev]

Exactly what I needed. Thanks..

Just for cosmetic sake, is there a way to disply 0 instead of (null) for the missing months?

 

[markros]

Sure - in either of the methods use COALESCE(..,0) as [Jan], etc.

select ID, 
coalesce(Jan,0) as Jan, coalesce(Feb,0) as Feb,
coalesce(Mar,0) as Mar from MyTable
PIVOT (sum(DaysOfCount) FOR [Month] IN ([Jan],[Feb],[Mar])) pvt

Similar solution for the other method.

PluralSight Learning Library