use ProduceManageSystem go if object_id(N'A',N'U') is not null drop table A go create table A ( id int, [level] int ) go insert into A select 1, 1 union all select 1 ,2 union all select 1 ,3 union all select 2 ,2 union all select 2 ,4 go select id, max(case when [level]=1 then [level] else 0 end) as level1, max(case when [level]=2 then [level] else 0 end) as level2, max(case when [level]=3 then [level] else 0 end) as level3, max(case when [level]=4 then [level] else 0 end) as level4 from A group by id drop table A /* (5 行受影响) id level1 level2 level3 level4 ----------- ----------- ----------- ----------- ----------- 1 1 2 3 0 2 0 2 0 4(2 行受影响) */
--> 测试数据:#a if object_id('tempdb.dbo.#a') is not null drop table #a create table #a([id] int,[level] int) insert #a select 1,1 union all select 1,2 union all select 1,3 union all select 2,2 union all select 2,4select * from #a declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , max(case when level = ''' + ltrim([level]) + ''' then [level] else 0 end) as [level' + ltrim([level]) + ']' from (select distinct [level] from #a) as a set @sql = @sql + ' from #a group by id' exec(@sql)
use ProduceManageSystem
go
if object_id(N'A',N'U') is not null drop table A
go
create table A
(
id int,
[level] int
)
go
insert into A
select 1, 1 union all
select 1 ,2 union all
select 1 ,3 union all
select 2 ,2 union all
select 2 ,4
go
select id,
max(case when [level]=1 then [level]
else 0 end) as level1,
max(case when [level]=2 then [level]
else 0 end) as level2,
max(case when [level]=3 then [level]
else 0 end) as level3,
max(case when [level]=4 then [level]
else 0 end) as level4
from A
group by id
drop table A
/*
(5 行受影响)
id level1 level2 level3 level4
----------- ----------- ----------- ----------- -----------
1 1 2 3 0
2 0 2 0 4(2 行受影响)
*/
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a([id] int,[level] int)
insert #a
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 2,4select * from #a
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case when level = ''' + ltrim([level]) + ''' then [level] else 0 end) as [level' + ltrim([level]) + ']'
from (select distinct [level] from #a) as a
set @sql = @sql + ' from #a group by id'
exec(@sql)