经典sql
行转列:
--测试数据
create table 基础表(姓名 nvarchar(10),课程名 nvarchar(20),成绩 decimal(10,1))
insert 基础表 select '王家喜','计算机基础',69.0
union all select '王家喜','*理论',74.0
union all select '王家喜','英语(上)',86.0
union all select '王家喜','普通逻辑学',91.0
union all select '施春林','立法学教程',60.0
union all select '施春林','经管原理' ,73.0
union all select '施春林','英语(上)',73.0
union all select '施春林','普通逻辑学',90.0
go
--查询
declare @s varchar(8000),@i varchar(10)
select top 1 @s='',@i=count(*)
from 基础表 group by 姓名 order by count(*) desc
while @i>0
select @s=',[课程'+@i+']=max(case when id='+@i+' then 课程名 else '''' end),
[成绩'+@i+']=max(case when id='+@i+' then 成绩 end)'+@s ,@i=@i-1
exec(' select 姓名,课程名,成绩,id=0 into #t from 基础表 order by 姓名
declare @i int,@姓名 varchar(10)
update #t set @i=case when @姓名=姓名 then @i+1 else 1 end,id=@i,@姓名=姓名
select 姓名'+@s+' from #t group by 姓名')
go
--删除测试
select * from table_goods
drop table 基础表
select * from table_goods
declare @str varchar(1000)
set @str='select id,visit '
select @str=@str+',min(case when goods='+ Cast(goods As Varchar) +' then goods_id else Null end) as goods_id'+ Cast(goods As Varchar)
+',min(case when goods='+ Cast(goods As Varchar) +' then name else Null end) as goods'+ Cast(goods As Varchar)
from table_goods group by goods
select @str=@str+' from table_goods group by id,visit order by id,visit'
print @str exec(@str)
select id,visit ,max(case when goods=1 then goods_id else Null end) as goods_id1,
max(case when goods=1 then name else Null end) as goods1,
max(case when goods=2 then goods_id else Null end) as goods_id2,
max(case when goods=2 then name else Null end) as goods2,
max(case when goods=3 then goods_id else Null end) as goods_id3,
max(case when goods=3 then name else Null end) as goods3,
max(case when goods=4 then goods_id else Null end) as goods_id4,
max(case when goods=4 then name else Null end) as goods4
from table_goods
group by id,visit order by id,visit