create table tb(unit nvarchar(10),cnt int) goinsert into tb select 'A',2 insert into tb select 'R',1 insert into tb select 'T',6 insert into tb select 'A',8 insert into tb select 'B',2 insert into tb select 'R',9SELECT 'A'=SUM(CASE WHEN UNIT ='A' THEN CNT END), 'R'=SUM(CASE WHEN UNIT ='R' THEN CNT END), 'T'=SUM(CASE WHEN UNIT ='T' THEN CNT END), 'B'=SUM(CASE WHEN UNIT ='B' THEN CNT END) FROM tb ----- A R T B 10 10 6 2
SELECT sum(cnt),unit FROM tb group by unit 再用程序把它写横
那就动态 declare @sql varchar(8000)select @sql =isnull(@sql+',','')+'sum(case when UNIT=''' + UNIT + ''' then cnt else 0 end) [' + UNIT + ']' from (select distinct UNIT from tb) as a set @sql = @sql + ' from tb' exec('select '+@sql)
create table tb(unit nvarchar(10),cnt int)
goinsert into tb select 'A',2
insert into tb select 'R',1
insert into tb select 'T',6
insert into tb select 'A',8
insert into tb select 'B',2
insert into tb select 'R',9SELECT 'A'=SUM(CASE WHEN UNIT ='A' THEN CNT END),
'R'=SUM(CASE WHEN UNIT ='R' THEN CNT END),
'T'=SUM(CASE WHEN UNIT ='T' THEN CNT END),
'B'=SUM(CASE WHEN UNIT ='B' THEN CNT END)
FROM tb
-----
A R T B
10 10 6 2
再用程序把它写横
那就动态
declare @sql varchar(8000)select @sql =isnull(@sql+',','')+'sum(case when UNIT=''' + UNIT + ''' then cnt else 0 end) [' + UNIT + ']'
from (select distinct UNIT from tb) as a
set @sql = @sql + ' from tb'
exec('select '+@sql)