现有数据表:table1(A,B),查询后显示的数据集的结构为table2(A,B1,B2,B3,B4)
`table1 table2
A B A B1 B2 B3 B4
1 a1 1 a1 a2 null null
1 a2 2 b1 b2 b3 null
2 b1 ...
2 b2
2 b3
...
现在要将Tble1的内容通过查询,显示成table2的样子,请问怎么实现?
`table1 table2
A B A B1 B2 B3 B4
1 a1 1 a1 a2 null null
1 a2 2 b1 b2 b3 null
2 b1 ...
2 b2
2 b3
...
现在要将Tble1的内容通过查询,显示成table2的样子,请问怎么实现?
insert into @table1 select 1,'a1'
insert into @table1 select 1,'a2'
insert into @table1 select 2,'b1'
insert into @table1 select 2,'b2'
insert into @table1 select 2,'b3'select
a.A,
B1=max(case a.num when 1 then B end),
B1=max(case a.num when 2 then B end),
B1=max(case a.num when 3 then B end),
B1=max(case a.num when 4 then B end)
from
(select b.*,(select count(*) from @table1 where A=b.A and B>=b.B) as num from @table1 b) a
group by
a.A/*
A B1 B1 B1 B1
----------- -------- -------- -------- --------
1 a2 a1 NULL NULL
2 b3 b2 b1 NULL
*/
INSERT table1
select 1,'a1' union all
select 1,'a2' union all
select 2,'b1' union all
select 2,'b2' union all
select 2,'b3' union all
select 2,'b4'select *,rowid=(select count(*)+ 1 from table1 where a = aa.a and b < aa.b)
into #
from table1 aadeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql +',[b'+cast(rowid as varchar)+']= max(case when rowid ='+cast(rowid as varchar)+' then b else Null end)'
from (select distinct rowid from #) aexec('select a'+@sql + ' from # group by a')
drop table table1,#/*
a b1 b2 b3 b4
----------- ---- ---- ---- ----
1 a1 a2 NULL NULL
2 b1 b2 b3 b4
*/
go
select 1 as A, 'a1' as B
into table1
union select 1, 'a2'
union select 2, 'b1'
union select 2, 'b2'
union select 2, 'b3'select A, min(case C when 1 then B end) as B1, min(case C when 2 then B end) as B2,
min(case C when 3 then B end) as B3, min(case C when 4 then B end) as B4
from (select *, (select count(*) + 1 from table1 where A = t1.A and B < t1.B) as C
from table1 t1) t2
group by A
/*
A B1 B2 B3 B4
1 a1 a2 NULL NULL
2 b1 b2 b3 NULL
*/drop table table1