SQL Query - Along the lines of a pivot table

Question

I have a mock up of a sql query that will represent a real sql query.

Create table #tmp
(
 Atype varchar(10),
 Btype varchar(10)
)

insert into #tmp values ('a','x')
insert into #tmp values ('b','x')
insert into #tmp values ('a','y')
insert into #tmp values ('a','y')
insert into #tmp values ('b','z')
insert into #tmp values ('b','y')

select atype, btype,count(*) as count
from #tmp
group by atype, btype
order by atype

drop table #tmp

This will give me the result of:

atype btype count
-----------------
a     x     1
a     y     2
b     x     1
b     y     1
b     z     1

What i am looking for after this is to be able to create a report that basically looks like:

atype|x| y| z
-------------
a    |1| 2| 0
b    |1| 1| 1

I am sure you can do this using come crazy t-sql code but i am struggeling to get it right.

EDIT:

I agree that you can use the TSQL PIVOT command but what happens when Btype is variable. Meaning that i dont know how many types there will be?

Answer 1

SELECT
  atype, 
  SUM(CASE btype WHEN 'x' THEN 1 ELSE 0 END) AS x,
  SUM(CASE btype WHEN 'y' THEN 1 ELSE 0 END) AS y,
  SUM(CASE btype WHEN 'z' THEN 1 ELSE 0 END) AS z
FROM
  #tmp
group by 
  atype
order by 
  atype

Answer 2

It seems that a direct pivot is supported only by SQL Server 2005 , as shown here

Here is how it can be done with Oracle - http://www.adp-gmbh.ch/ora/sql/examples/pivot.html

Answer 3

See this for a stored procedure for MySQL that produces a cross-tabulation:

http://www.futhark.ch/mysql/106.html

Answer 4

@Tomalak's answer is nice, but there's nothing crazy about it, just plain old SQL.

Here's the really crazy T-SQL code you asked for, in case you were interested:

SELECT  atype, [x], [y], [z]
FROM    (
        SELECT  atype, btype
        FROM    #tmp
        ) t
PIVOT
        (
        COUNT(btype)
        FOR btype IN ([x], [y], [z])
        ) AS PivotTable