Pivot row data and count into columns

Question

I've got two columns of data as below:

Client	Type
A	Pencil
A	Ruler
A	Pen
B	Pencil
B	Ruler
C	Pencil
C	Pen

I want to pivot 'Type' and make them the columns and group by 'Client'. Result would look like below:

Client	Count(Pencil)	Count(Ruler)	Count(Pen)
A	1	1	1
B	1	1	0
C	1	0	1

How can I achieve this on SQL Server?

Thanks

Answer 1

In SQL Server, you can express this as:

select client,
       sum(case when type = 'Pencil' then 1 else 0 end) as pencil,
       sum(case when type = 'Ruler' then 1 else 0 end) as ruler,
       sum(case when type = 'Pen' then 1 else 0 end) as pen
from t
group by client;

Answer 2

Please see MSDN for reference.

SELECT Client, [Pencil], [Ruler], [Pen]
FROM  
(
  SELECT Client, Type
  FROM tableName
) AS SourceTable  
PIVOT  
(  
  count(Type)  
  FOR Type IN ([Pencil], [Ruler], [Pen])  
) AS PivotTable;