--写这样的语句很费脑子的select case when max(name1) is null then 'Null' else max(name1) end +'_'
+case when max(name2) is null then 'Null' else max(name2) end +'_'
+case when max(name3) is null then 'Null' else max(name3) end as Name
from (
select (select count(*) from 表1 where classid=a.classid and id<=a.id) as 序号,
cast(case when class=1 then name else '' end as varchar(100)) as name1,
cast(case when class=2 then name else '' end as varchar(100)) as name2,
cast(case when class=3 then name else '' end as varchar(100)) as name3
from 表1 a
) as t
group by 序号
order by 序号
--测试成功
--改成动态相信不是难事，好多人都会的
declare @表1 table (
id int,
classid int,
name varchar(5)
)insert @表1 select
1, 1, 'S'
union all select
2, 1, 'M'
union all select
3, 1, 'L'
union all select
4, 2, '37'
union all select
5, 2, '38'
union all select
6, 2, '39'
union all select
7, 2, '40'
union all select
8, 2, '41'
union all select
9, 2, '42'
union all select
10, 3, '100'
union all select
11, 3, '105'
union all select
12, 3, '110'
union all select
13, 3, '115'
union all select
14, 3, '120' select
case when isnull(max(name1),'')='' then 'Null' else max(name1) end +'_'
+case when isnull(max(name3),'')='' then 'Null' else max(name3) end +'_'
+case when isnull(max(name2),'')='' then 'Null' else max(name2) end as Name
from (
select (select count(*) from @表1 where classid=a.classid and id<=a.id) as 序号,
cast(case when classid=1 then name else '' end as varchar(100)) as name1,
cast(case when classid=2 then name else '' end as varchar(100)) as name2,
cast(case when classid=3 then name else '' end as varchar(100)) as name3
from @表1 a
) as t
group by 序号
order by 序号--结果
Name
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S_100_37
M_105_38
L_110_39
Null_115_40
Null_120_41
Null_Null_42（所影响的行数为 6 行）
insert @t select
1, 1 ,'S' union all select
2, 1 ,'M' union all select
3, 1 ,'L' union all select
4, 2 ,'37' union all select
5, 2 ,'38' union all select
6, 2 ,'39' union all select
7, 2 ,'40' union all select
8, 2 ,'41' union all select
9, 2 ,'42' union all select
10, 3 ,'100' union all select
11, 3 ,'105' union all select
12, 3 ,'110' union all select
13, 3 ,'115' union all select
14, 3 ,'120' select *,[count] = (select count(1)+1 from @t where a.classid = classid and id< a.id)
into #
from @t a
select isnull(c.[name],'Null') +'_'+ isnull(a.[name],'Null') +'_'+ isnull(b.[name],'Null')
from # a
left join # b on a.classid = b.classid + 1 and a.count = b.count
left join # c on a.classid = c.classid + 2 and a.count = c.count
where b.classid = 2
order by b.countdrop table #
/*
--------------
S_100_37
M_105_38
L_110_39
Null_115_40
Null_120_41（所影响的行数为 5 行）*/
insert into tb values(1 ,1 ,'S')
insert into tb values(2 ,1 ,'M')
insert into tb values(3 ,1 ,'L')
insert into tb values(4 ,2 ,'37')
insert into tb values(5 ,2 ,'38')
insert into tb values(6 ,2 ,'39')
insert into tb values(7 ,2 ,'40')
insert into tb values(8 ,2 ,'41')
insert into tb values(9 ,2 ,'42')
insert into tb values(10,3 ,'100')
insert into tb values(11,3 ,'105')
insert into tb values(12,3 ,'110')
insert into tb values(13,3 ,'115')
insert into tb values(14,3 ,'120')
goselect m1.px , name = isnull(m2.name , 'null') + '_' + m1.name from
(
select t3.px , name = isnull(t2.name,'null') + '_' + t3.name from
(select * from (select px = (select count(1) from tb where classid = t.classid and id < t.id) + 1 , * from tb t) m where classid = 2) t3
full join
(select * from (select px = (select count(1) from tb where classid = t.classid and id < t.id) + 1 , * from tb t) m where classid = 3) t2
on t3.px = t2.px
) m1
full join
(select * from (select px = (select count(1) from tb where classid = t.classid and id < t.id) + 1 , * from tb t) m where classid = 1) m2
on m1.px = m2.pxdrop table tb/*
px name
----------- --------------------------------
1 S_100_37
2 M_105_38
3 L_110_39
4 null_115_40
5 null_120_41
6 null_null_42(6 行受影响)
*/
create table t (id int,classid int, name varchar(100) )
insert t select 1,1,'S'
union all select 2, 1, 'M'
union all select 3, 1, 'L'
union all select 4, 2, '37'
union all select 5, 2, '38'
union all select 6, 2, '39'
union all select 7, 2, '40'
union all select 8, 2, '41'
union all select 9, 2, '42'
union all select 10, 3, '100'
union all select 11, 3, '105'
union all select 12, 3, '110'
union all select 13, 3, '115'
union all select 14, 3, '120'
select * from tgodeclare @s1 varchar(8000),@s2 varchar(8000)
declare @i1 int,@i2 int
set @i2 = 0
set @s1 = ''
set @s2 = ''
select * into #tt from t order by classid
update #tt set id = case when @i2 = classid then @i1 else 1 end,@i1 = case when @i2 = classid then @i1+1 else 2 end,@i2 = classid
select @s1= @s1 + ',max(case classid when ' + rtrim(classid) + ' then name else '''' end) as a' + rtrim(classid),@s2 = @s2 + '+''_''+case a'+rtrim(classid)+' when '''' then ''NULL'' ELSE a'+rtrim(classid)+' END'
from (select classid from #tt group by classid ) as a
set @s2 = right(@s2,len(@s2)-5)
set @s1 = 'select id'+@s1+' from #tt group by id '
exec('select '+@s2+' as name from ('+@s1+') b')
drop table #tt
drop table t
/*
name
------------
S_37_100
M_38_105
L_39_110
NULL_40_115
NULL_41_120
NULL_42_NULL
*/