sql 行列转换
---行列转换
create table m (
matid int,
qty int,
lid int
)
insert into m values(10011,1,101);
insert into m values(20012,2,102);
insert into m values(10011,2,102);
insert into m values(10011,1,102);
create table l(
lid int,
name varchar(20)
)
insert into l values(101,'北京');
insert into l values(102,'太原');
create table s(
sid int,
name varchar(20)
)
insert into s values(11,'西服');
insert into s values(12,'皮鞋');
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(Name)+'=sum(case when m.lid='+rtrim(lid)+' then 1 else 0 end)' from L
exec('select s.Name as 类别'+@s+'from m inner join s on s.sid=right(m.matid,2) group by s.Name')
学习拓展:http://topic.csdn.net/u/20111018/15/9d404b39-b409-45d0-aae1-d037c25bad46.html?66630