select c_Level ,col1=max(case id%10 when 0 then c_No end) ,col2=max(case id%10 when 1 then c_No end) ,col3=max(case id%10 when 2 then c_No end) ,col4=max(case id%10 when 3 then c_No end) ,col5=max(case id%10 when 4 then c_No end) ,col6=max(case id%10 when 5 then c_No end) ,col7=max(case id%10 when 6 then c_No end) ,col8=max(case id%10 when 7 then c_No end) ,col9=max(case id%10 when 8 then c_No end) ,col10=max(case id%10 when 9 then c_No end) from( select *,id=(select count(*) from 表 where c_Level=a.c_Level and c_No<=a.c_No)-1 from 表 a )a group by c_Level,id/10
--生成测试数据 create table t( c_No int, c_Level varchar(2))insert into t select 101,'01' insert into t select 102,'01' insert into t select 103,'01' insert into t select 104,'01' insert into t select 105,'01' insert into t select 106,'01' insert into t select 107,'01' insert into t select 108,'01' insert into t select 109,'01' insert into t select 110,'01' insert into t select 111,'01' insert into t select 201,'02' insert into t select 202,'02' insert into t select 203,'02' insert into t select 301,'03' --执行查询过程 select identity(int,1,1) as id,0 as nid ,c_No,c_Level into #t from t order by c_Level,c_Noupdate #t set nid = (a.id-b.id+1) from #t a inner join (select c_Level,min(id) as id from #t group by c_Level) b on a.c_Level = b.c_Level select c_Level, col1 = max(case when nid%10 = 1 then c_No end), col2 = max(case when nid%10 = 2 then c_No end), col3 = max(case when nid%10 = 3 then c_No end), col4 = max(case when nid%10 = 4 then c_No end), col5 = max(case when nid%10 = 5 then c_No end), col5 = max(case when nid%10 = 5 then c_No end), col7 = max(case when nid%10 = 7 then c_No end), col8 = max(case when nid%10 = 8 then c_No end), col9 = max(case when nid%10 = 9 then c_No end), col10= max(case when nid%10 = 0 then c_No end) from #t group by c_Level,nid/10 order by c_Level,nid/10 --输出结果 /* c_Level col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 ------------------------------------------------------------------ 01 101 102 103 104 105 106 107 108 109 110 01 110 null null null null null null null null null 02 201 202 203 null null null null null null null 03 301 null null null null null null null null null */
楼上的办法是针对同一c_Level的c_No存在不连续情况的处理办法
哗,原来大哥们都在。TO pbsql(风云) :一般SUM的交叉我是查过的,也可以做出来,只是这个定列的我想不出来。 TO zjcxc(邹建): 刚试了一下,你的测试能过。我加入其它字段试试,TKS! TO libin_ftsafe(子陌红尘) :试过了,你的也可以,不过好象数据不同时有些数据排列有问题谢谢各位,试完没问题就结贴.
http://community.csdn.net/Expert/topic/3839/3839358.xml?temp=.2946436
,col1=max(case id%10 when 0 then c_No end)
,col2=max(case id%10 when 1 then c_No end)
,col3=max(case id%10 when 2 then c_No end)
,col4=max(case id%10 when 3 then c_No end)
,col5=max(case id%10 when 4 then c_No end)
,col6=max(case id%10 when 5 then c_No end)
,col7=max(case id%10 when 6 then c_No end)
,col8=max(case id%10 when 7 then c_No end)
,col9=max(case id%10 when 8 then c_No end)
,col10=max(case id%10 when 9 then c_No end)
from(
select *,id=(select count(*) from 表 where c_Level=a.c_Level and c_No<=a.c_No)-1
from 表 a
)a group by c_Level,id/10
create table t(
c_No int,
c_Level varchar(2))insert into t select 101,'01'
insert into t select 102,'01'
insert into t select 103,'01'
insert into t select 104,'01'
insert into t select 105,'01'
insert into t select 106,'01'
insert into t select 107,'01'
insert into t select 108,'01'
insert into t select 109,'01'
insert into t select 110,'01'
insert into t select 111,'01'
insert into t select 201,'02'
insert into t select 202,'02'
insert into t select 203,'02'
insert into t select 301,'03'
--执行查询过程
select identity(int,1,1) as id,0 as nid ,c_No,c_Level into #t from t order by c_Level,c_Noupdate #t
set
nid = (a.id-b.id+1)
from
#t a
inner join
(select c_Level,min(id) as id from #t group by c_Level) b
on
a.c_Level = b.c_Level select
c_Level,
col1 = max(case when nid%10 = 1 then c_No end),
col2 = max(case when nid%10 = 2 then c_No end),
col3 = max(case when nid%10 = 3 then c_No end),
col4 = max(case when nid%10 = 4 then c_No end),
col5 = max(case when nid%10 = 5 then c_No end),
col5 = max(case when nid%10 = 5 then c_No end),
col7 = max(case when nid%10 = 7 then c_No end),
col8 = max(case when nid%10 = 8 then c_No end),
col9 = max(case when nid%10 = 9 then c_No end),
col10= max(case when nid%10 = 0 then c_No end)
from
#t
group by
c_Level,nid/10
order by
c_Level,nid/10
--输出结果
/*
c_Level col1 col2 col3 col4 col5 col6 col7 col8 col9 col10
------------------------------------------------------------------
01 101 102 103 104 105 106 107 108 109 110
01 110 null null null null null null null null null
02 201 202 203 null null null null null null null
03 301 null null null null null null null null null
*/
TO zjcxc(邹建): 刚试了一下,你的测试能过。我加入其它字段试试,TKS!
TO libin_ftsafe(子陌红尘) :试过了,你的也可以,不过好象数据不同时有些数据排列有问题谢谢各位,试完没问题就结贴.