行变列

[wqvbjhc]

OWENER_ID COMMODITY_ID STATUS
飞扬电脑城 E5300 1
飞扬电脑城 E5300 2
飞扬电脑城 E5300 2
飞扬电脑城 E5300 1
飞扬电脑城 E5300 1
飞扬电脑城 E5300 1
statue 只有 1 跟 2

想要得到下面的一个视图，如何写sql语句啊
OWENER_ID COMMODITY_ID STATUS1 STATUS2
飞扬电脑城 E5300 4 2

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(OWENER_ID varchar(10), COMMODITY_ID varchar(8), STATUS int)
insert into #
select '飞扬电脑城', 'E5300', 1 union all
select '飞扬电脑城', 'E5300', 2 union all
select '飞扬电脑城', 'E5300', 2 union all
select '飞扬电脑城', 'E5300', 1 union all
select '飞扬电脑城', 'E5300', 1 union all
select '飞扬电脑城', 'E5300', 1

select OWENER_ID, COMMODITY_ID,
	STATUS1 = sum(case STATUS when 1 then 1 else 0 end),
	STATUS2 = sum(case STATUS when 2 then 1 else 0 end)
from # group by OWENER_ID, COMMODITY_ID

/*
OWENER_ID  COMMODITY_ID STATUS1     STATUS2
---------- ------------ ----------- -----------
飞扬电脑城 E5300        4           2
*/

[wqvbjhc]

SQL就是高手云集，回答问题真快。谢谢