Count results in colums instead of rows  
Author Message
Hoveler





PostPosted: 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





PostPosted: Tue May 30 05:29:28 CDT 2006 Top

SQL Server Developer >> Count results in colums instead of rows
> 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


SELECT Code,
status0 = SUM(CASE status WHEN 0 THEN 1 ELSE 0 END),
status0 = SUM(CASE status WHEN 1 THEN 1 ELSE 0 END)
FROM tbl
GROUP BY Code


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Uri





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: Tue May 30 17:04:45 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)

That was not in your original post. :-) If Status can have an infinite
number of values, you are asking for a dynamic crosstab. Either you
start hacking dynamic SQL, or you go to http://www.rac4sql.net for
a popular third-party tool.


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx