select
code,
ID2 = (ID1/10)+1,
v0 = max(case ID1%10 when 0 then value end),
v1 = max(case ID1%10 when 1 then value end),
v2 = max(case ID1%10 when 2 then value end),
v3 = max(case ID1%10 when 3 then value end),
v4 = max(case ID1%10 when 4 then value end),
v5 = max(case ID1%10 when 5 then value end),
v6 = max(case ID1%10 when 6 then value end),
v7 = max(case ID1%10 when 7 then value end),
v8 = max(case ID1%10 when 8 then value end),
v9 = max(case ID1%10 when 9 then value end)
from
原表
group by
code,(ID1/10)+1
code,
ID2 = (ID1/10)+1,
v0 = max(case ID1%10 when 0 then value end),
v1 = max(case ID1%10 when 1 then value end),
v2 = max(case ID1%10 when 2 then value end),
v3 = max(case ID1%10 when 3 then value end),
v4 = max(case ID1%10 when 4 then value end),
v5 = max(case ID1%10 when 5 then value end),
v6 = max(case ID1%10 when 6 then value end),
v7 = max(case ID1%10 when 7 then value end),
v8 = max(case ID1%10 when 8 then value end),
v9 = max(case ID1%10 when 9 then value end)
from
原表
group by
code,(ID1/10)+1
create table ta(code varchar(10),ID1 int,value int)
go
insert ta select 'A', 0, 100 union all select 'A', 1, 200
union all select 'A', 3, 120 union all select 'B', 1, 80
union all select 'B', 7, 120 union all select 'B', 13, 121
union all select 'C', 3, 20 union all select 'C', 25, 110
--查询
select code, id2=(id1/10+1)
,v0=max(case when code=tt.code and id1%10=0 then value else null end)
,v1=max(case when code=tt.code and id1%10=1 then value else null end)
,v2=max(case when code=tt.code and id1%10=2 then value else null end)
,v3=max(case when code=tt.code and id1%10=3 then value else null end)
,v4=max(case when code=tt.code and id1%10=4 then value else null end)
,v5=max(case when code=tt.code and id1%10=5 then value else null end)
,v6=max(case when code=tt.code and id1%10=6 then value else null end)
,v7=max(case when code=tt.code and id1%10=7 then value else null end)
,v8=max(case when code=tt.code and id1%10=8 then value else null end)
,v9=max(case when code=tt.code and id1%10=9 then value else null end)
from ta as tt
group by code, (id1/10+1)
order by code
--清除
drop table ta
create table #T(code varchar(10),ID1 int,value int)
insert into #T select 'A',0 ,100
insert into #T select 'A',1 ,200
insert into #T select 'A',3 ,120
insert into #T select 'B',1 , 80
insert into #T select 'B',7 ,120
insert into #T select 'B',13,121
insert into #T select 'C',3 ,20
insert into #T select 'C',25,110
--执行查询
select
code,
ID2 = (ID1/10)+1,
v0 = max(case ID1%10 when 0 then value end),
v1 = max(case ID1%10 when 1 then value end),
v2 = max(case ID1%10 when 2 then value end),
v3 = max(case ID1%10 when 3 then value end),
v4 = max(case ID1%10 when 4 then value end),
v5 = max(case ID1%10 when 5 then value end),
v6 = max(case ID1%10 when 6 then value end),
v7 = max(case ID1%10 when 7 then value end),
v8 = max(case ID1%10 when 8 then value end),
v9 = max(case ID1%10 when 9 then value end)
from
#T
group by
code,(ID1/10)+1
order by
code,(ID1/10)+1
--输出结果
/*
code ID2 v0 v1 v2 v3 v4 v5 v6 v7 v8 v9
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
A 1 100 200 NULL 120 NULL NULL NULL NULL NULL NULL
B 1 NULL 80 NULL NULL NULL NULL NULL 120 NULL NULL
B 2 NULL NULL NULL 121 NULL NULL NULL NULL NULL NULL
C 1 NULL NULL NULL 20 NULL NULL NULL NULL NULL NULL
C 3 NULL NULL NULL NULL NULL 110 NULL NULL NULL NULL
*/
insert test
select 'A',0,100 union all
select 'A',1,200 union all
select 'A',3,120 union all
select 'B',1,80 union all
select 'B',7,20 union all
select 'B',13,121 union all
select 'C',3,20 union all
select 'C',25,110select code,ID2=(id1/10)+1,
v0=sum(case id1%10 when 0 then value else 0 end),
v1=sum(case id1%10 when 1 then value else 0 end),
v2=sum(case id1%10 when 2 then value else 0 end),
v3=sum(case id1%10 when 3 then value else 0 end),
v4=sum(case id1%10 when 4 then value else 0 end),
v5=sum(case id1%10 when 5 then value else 0 end),
v6=sum(case id1%10 when 6 then value else 0 end),
v7=sum(case id1%10 when 7 then value else 0 end),
v8=sum(case id1%10 when 8 then value else 0 end),
v9=sum(case id1%10 when 9 then value else 0 end)
from test group by code,(id1/10)+1
order by code,(id1/10)+1/*
code ID2 v0 v1 v2 v3 v4 v5 v6 v7 v8 v9
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 1 100 200 0 120 0 0 0 0 0 0
B 1 0 80 0 0 0 0 0 20 0 0
B 2 0 0 0 121 0 0 0 0 0 0
C 1 0 0 0 20 0 0 0 0 0 0
C 3 0 0 0 0 0 110 0 0 0 0(所影响的行数为 5 行)*/
drop table test