行列转换

来源：互联网发布：亿乐社区源码编辑：程序博客网时间：2024/04/30 06:44

drop table tablea

create table tablea
(name varchar(50),
a varchar(50),
b int
)

insert into tablea(name,a,b)
select '入库','单位a',3
insert into tablea(name,a,b)
select '入库','单位e',3
insert into tablea(name,a,b)
select '出库','单位a',4
insert into tablea(name,a,b)
select '出库','单位d',3
insert into tablea(name,a,b)
select '出库','单位a',3
insert into tablea(name,a,b)
select '入库','单位b',2
insert into tablea(name,a,b)
select '出库','单位a',5
insert into tablea(name,a,b)
select '入库','单位c',3
insert into tablea(name,a,b)
select '出库','单位c',6
select * from tablea

-------------方案
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+a
               +'=isnull(rtrim(max(case   when a='
               + QUOTENAME(a,'''')
               +' then b end)),'''')'

from tablea group by a
exec('select name '+@s+' from tablea group by name')

drop table tablea