Multiple column result from one table

[Spontex]

Hi,
I have a table like this :

TimeID Application Login State
1 App1 login1 0
1 App2 login2 1
1 App3 login3 1
1 App1 login4 0
1 App1 login5 1
1 App4 login1 0
2 App1 login1 0
2 App2 login2 0
2 App3 login3 1
2 App1 login4 0
2 App1 login5 1
2 App4 login1 0
3 App1 login1 0
3 App2 login2 1
3 App3 login3 1
3 App1 login4 0
3 App1 login5 1
3 App4 login1 0

And I want a result like this
TimeID (state=0) (state=1)
1 3 3
2 4 2
3 3 3

I use this code :

set nocount on
declare @timeid1 int
declare @timeid2 int
declare @timeid int
declare @sessionstate int
declare @count1 int
declare @count2 int
declare @count int
declare @aux int

declare txt_cursor cursor for

select timeid, sessionstate , count(login) from metromaster
where sessionstate = 0
group by timeid, sessionstate
UNION
select timeid, sessionstate , count(login) from metromaster
where sessionstate = 1
group by timeid, sessionstate
order by timeid

open txt_cursor
select @aux = 0
fetch next from txt_cursor into @timeid, @sessionstate, @count
while (@@fetch_status = 0)
begin
if @aux = 0
begin
select @timeid1 = @timeid
select @count1 = @count
select @aux =1
end
else
begin
select @timeid2 = @timeid
select @count2 = @count
select @aux = 2
end
if @aux = 2
begin
--select @timeid1, @count1, @sessionstate, @count2, @timeid2
select @timeid1, @count1, @count2
select @aux = 0
end
fetch next from txt_cursor into @timeid, @sessionstate, @count

end
close txt_cursor
deallocate txt_cursor
set nocount off

But it create a lot of blank row and field header.
Does anyone know an other methode ??

Thanks.

[David Portas]

Re: Multiple column result from one table

SELECT timeid,
COUNT(CASE state WHEN 0 THEN 1 END) AS state_0,
COUNT(CASE state WHEN 1 THEN 1 END) AS state_1
FROM metromaster
GROUP BY timeid

--
David Portas
------------
Please reply only to the newsgroup
--