name project
张三 数学
李四 语文
王五 英语
张三 物理
name project1 project2
张三 数学 物理
李四 语文 null
王五 英语 null怎么样从第一张表的样子变成第二章表的样子,project不是一定的,可以增加
张三 数学
李四 语文
王五 英语
张三 物理
name project1 project2
张三 数学 物理
李四 语文 null
王五 英语 null怎么样从第一张表的样子变成第二章表的样子,project不是一定的,可以增加
SELECT @S=ISNULL(@S+',','')+'MAX(CASE WHEN PROJECT='''+PROJECT+''' THEN PROJECT END) AS PROJECT' FROM (SELECT DISTINCT PROJECT FROM TB) TEXEC('SELECT NAME,'+@SQL +' GROUP BY NAME')
if object_id('tempdb.dbo.#') is not null drop table #
create table #(name varchar(8), project varchar(8))
insert into #
select '张三', '数学' union all
select '李四', '语文' union all
select '王五', '英语' union all
select '张三', '物理'declare @max int, @i int, @cols varchar(max)
select top 1 @max = count(1) from # group by name order by 1 descset @i = 1
while @i<=@max
begin
set @cols = isnull(@cols+',','') + 'project'+ltrim(@i)+'=max(case id when '+ltrim(@i)+' then project end)'
set @i=@i+1
endexec ('select name,'+@cols+' from (select *,row_number()over(partition by name order by project)id from #) t group by name')/*
name project1 project2
-------- -------- --------
李四 语文 NULL
王五 英语 NULL
张三 数学 物理
*/
set @sql = 'select name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then project else null end) [project' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , (select count(1) from tb where name = t.name and project < t.project) + 1 as px from tb t)m) as a
set @sql = @sql + ' from (select t.* , (select count(1) from tb where name = t.name and project < t.project) + 1 as px from tb t)m group by name'
exec(@sql)
insert into tb
select '张三', '数学' union all
select '李四', '语文' union all
select '王五', '英语' union all
select '张三', '物理'
godeclare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then project else null end) [project' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , (select count(1) from tb where name = t.name and project < t.project) + 1 as px from tb t)m) as a
set @sql = @sql + ' from (select t.* , (select count(1) from tb where name = t.name and project < t.project) + 1 as px from tb t)m group by name'
exec(@sql) drop table tb/*
name project1 project2
-------- -------- --------
李四 语文 NULL
王五 英语 NULL
张三 数学 物理
*/