Count results in colums instead of rows |
|
Author |
Message |
Hoveler
|
Posted: Tue May 30 05:29:28 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
Is it possible to show results of a COUNT in columns rather than in rows
Example:
Query:
SELECT Code, Status, Count(Status) FROM MyTable
GROUP By Code, Status
returns:
Code Status Count
X33 0 312
X33 1 45
X77 0 12
X77 1 144
What I want:
Code Status Count Status Count
X33 0 312 1 45
X77 0 12 1 144
Or:
Code Count Status 0 Count Status 1
X33 312 45
X77 12 144
Thanks,
Bart
SQL Server212
|
|
|
|
|
Erland
|
Posted: Tue May 30 05:29:28 CDT 2006 |
Top |
|
|
|
Uri
|
Posted: Tue May 30 05:29:34 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
Bart
create table #test (id int not null primary key,
code varchar(10),status int, [count] int)
insert into #test values (1,'X33',0,312)
insert into #test values (2,'X33',1,35)
insert into #test values (3,'X77',0,12)
insert into #test values (4,'X77',1,144)
---1
select * from #test t join #test t1
on t.code=t1.code and t.id <t1.id
---2
select code,max(case when status=0 then [count] end) c1,
max(case when status=1 then [count] end) c2
from #test
group by codes
> Is it possible to show results of a COUNT in columns rather than in rows
>
>
> Example:
>
> Query:
> SELECT Code, Status, Count(Status) FROM MyTable
> GROUP By Code, Status
>
> returns:
>
> Code Status Count
> X33 0 312
> X33 1 45
> X77 0 12
> X77 1 144
>
> What I want:
>
> Code Status Count Status Count
> X33 0 312 1 45
> X77 0 12 1 144
>
> Or:
>
> Code Count Status 0 Count Status 1
> X33 312 45
> X77 12 144
>
> Thanks,
> Bart
>
|
|
|
|
|
Omnibuzz
|
Posted: Tue May 30 05:32:01 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
try this
SELECT Code, sum(case when Status = 0 then 1 else 0 end) as countstatus0,
sum(case when Status = 1 then 1 else 0 end) as countstatus1
FROM MyTable
GROUP By Code
Hope this helps.
|
|
|
|
|
amish
|
Posted: Tue May 30 06:21:18 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
If you are using SQL Sever 2005 you can use
Pivot operator.
select code, [0] as count_status1, [1] as count_status2
from test
pivot
(sum (count)
for status in ([0],[1])) as pvt
Regards
Amish Shah
|
|
|
|
|
Bart
|
Posted: Tue May 30 06:34:09 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
What if Status has a range from 0 to n with gaps. (ie. 0, 1, 4 ,21, 66, 67,
etc)
> try this
>
> SELECT Code, sum(case when Status = 0 then 1 else 0 end) as countstatus0,
> sum(case when Status = 1 then 1 else 0 end) as countstatus1
> FROM MyTable
> GROUP By Code
> Hope this helps.
|
|
|
|
|
Omnibuzz
|
Posted: Tue May 30 08:59:01 CDT 2006 |
Top |
SQL Server Developer >> Count results in colums instead of rows
I think that cannot be solved even with the PIVOT in 2005 (since your IN
clause doesn't allow sub-queries) :)
we will have to go for dynamic sql..
> What if Status has a range from 0 to n with gaps. (ie. 0, 1, 4 ,21, 66, 67,
> etc)
>
>
> > try this
> >
> > SELECT Code, sum(case when Status = 0 then 1 else 0 end) as countstatus0,
> > sum(case when Status = 1 then 1 else 0 end) as countstatus1
> > FROM MyTable
> > GROUP By Code
> > Hope this helps.
>
>
>
|
|
|
|
|
Erland
|
Posted: Tue May 30 17:04:45 CDT 2006 |
Top |
|
|
|
|
|