MS SQL Qry - Using CAse

[bharadwajrv]

Hi,
i have a table(table1) like this

BatchID Status
------- ------
1001      1
1001      2
1001      2
1002      0
1002      3
1002      4

"Status" has value from 0 to 5..

i want to write a sql query
where i get the count of batch's status in a sinle row
like


Batch Status-0 Status-1 Status-2 .....
----- -------- -------- --------
1001     0       1         2

1002     1       0         0

something like this, where Each batch's all status values is displayed in a single row..
how 2 do this, plz help me out..

thanks in adv
Venu

[MAK]

Look BOL for cross tab and pivot table.

[skhanal]

The number of status you want to display has to be fixed for you to get the result using T-SQL, that is if you add a row with new status your query has to be changed.

[bharadwajrv]

Hi,
the status in the table are fixed, there will not be any addition to status.

can u plz help me out in building the query...

manu thanks
Venu

[skhanal]

If you look in BOL, you have similar query.

select batchid,
sum(case status when 0 then 1 else 0 end) as [status-0],
sum(case status when 1 then 1 else 0 end) as [status-1],
sum(case status when 2 then 1 else 0 end) as [status-2],
sum(case status when 3 then 1 else 0 end) as [status-3],
sum(case status when 4 then 1 else 0 end) as [status-4]
from table1
group by batchid