Loading

SQL技巧之行列转换

比如:
id     姓名      状态  
1      刘德华    1
2      刘德华    2
3      周华健    0
4      吴彦祖    1


在access中,用一条sql查询语句,生成结果为:


姓名    总数    状态0    状态1   状态2
刘德华   2       0          1       1
周华健   1       1          0       0
吴彦祖   1       0          1       0

答案一:

if not object_id('tb') is null
    drop table tb
Go
Create table tb([id] int,[姓名] nvarchar(3),[状态] int)
Insert tb
select 1,N'刘德华',1 union all
select 2,N'刘德华',2 union all
select 3,N'周华健',0 union all
select 4,N'吴彦祖',1
Go
select [姓名],
       count(*)总数,
       sum(case when [状态]=0 then 1 else 0 end )[状态0],
       sum(case when [状态]=1 then 1 else 0 end )[状态1],
       sum(case when [状态]=2 then 1 else 0 end )[状态2]
from tb
group by [姓名]

答案二:

select name,count(*),sum(iif(audit=0,1,0)), sum(iif(audit=1,1,0)),sum(iif(audit=2,1,0))
from tb
group by name

 

posted @ 2016-07-04 14:58  guwei4037  阅读(258)  评论(0编辑  收藏  举报