22,209
社区成员
发帖
与我相关
我的任务
分享
insert into enterList(广州医院) select studentName from resultList where companyName = '广州医院';
insert into enterList(中山医院) select studentName from resultList where companyName = '中山医院';
insert into enterList(大岭山医院) select studentName from resultList where companyName = '大岭山医院';
insert into enterList(海南人民医院) select studentName from resultList where companyName = '海南人民医院';
if OBJECT_ID(N'tempdb.dbo.#t') is not null
drop table #t
go
create table #t
(studentname nvarchar(20),
studentnum nvarchar(20),
studentcity nvarchar(20),
studentgender nvarchar(5),
companyname nvarchar(20),
studentgrade nvarchar(10))
insert into #t
select '张三','15209001','广东广州','男','广州医院','2015' union all
select '李四','15209002','广东深圳','男','中山医院','2015' union all
select '小芳','15209003','广东惠州','女','中山医院','2015' union all
select '王麻子','15209004','广东湛江','男','大岭山医院','2015' union all
select '周婷婷','15209005','广东河源','女','海南人民医院','2015'
select *
from (select studentname,companyname,rid=ROW_NUMBER()over(partition by companyname order by studentname)
from #t
) a pivot (max(studentname) for companyname in([广州医院],[中山医院],[大岭山医院],[海南人民医院]) )pt
----------------------------------------------------------------------------------------------------------------------------------
rid 广州医院 中山医院 大岭山医院 海南人民医院
-------------------- -------------------- -------------------- -------------------- --------------------
1 张三 李四 王麻子 周婷婷
2 NULL 小芳 NULL NULL
if OBJECT_ID(N'tempdb.dbo.#t') is not null
drop table #t
go
create table #t
(studentname nvarchar(20),
studentnum nvarchar(20),
studentcity nvarchar(20),
studentgender nvarchar(5),
companyname nvarchar(20),
studentgrade nvarchar(10))
insert into #t
select '张三','15209001','广东广州','男','广州医院','2015' union all
select '李四','15209002','广东深圳','男','中山医院','2015' union all
select '小芳','15209003','广东惠州','女','中山医院','2015' union all
select '王麻子','15209004','广东湛江','男','大岭山医院','2015' union all
select '周婷婷','15209005','广东河源','女','海南人民医院','2015'
declare @sql nvarchar(max)
with cte
as
(select *,ROW_NUMBER() over (partition by companyname order by (select 1)) as rn
from #t)
select
@sql=ISNULL(@sql+',','')+'max(case when companyname='''+companyname+''' then studentname else '''' end) as '+companyname
from (select companyname from #t group by companyname) as A
set @sql='with cte
as
(select *,ROW_NUMBER() over (partition by companyname order by (select 1)) as rn from #t)
select rn,'+@sql+' from cte group by rn'
exec(@sql)
--列数固定的用静态的行列转换
select 广州医院,中山医院,大岭山医院,海南人民医院
from resultList pivot( max(studentname) for companyname in (广州医院,中山医院,大岭山医院,海南人民医院) ) a
--列数不固定的用动态的行列转换
declare @s varchar(1000)
select @s = isnull(@s,'') + ',' +companyname from resultList group by companyname
select @s = 'select ' + stuff(@s,1,1,'') + ' from resultList pivot( max(studentname) for companyname in (' +stuff(@s,1,1,'')+ ') ) a'
exec (@s)