怎么实现这样的结果啊?
No Card_id No Card_id No Card_id
1 10001 6 10007 11 10012
2 10002 7 10008 ... ....
3 10004 8 10009
4 10005 9 10010
5 10006 10 10011
No Card_id No Card_id No Card_id
1 10001 6 10007 11 10012
2 10002 7 10008 ... ....
3 10004 8 10009
4 10005 9 10010
5 10006 10 10011
(no-1)/5 的数算行你有多少数据?
最后不对齐的话 可以空着我要导入到EXCEL中 显示上面的形式
insert into tb values(1 ,'10001')
insert into tb values(2 ,'10002')
insert into tb values(3 ,'10004')
insert into tb values(4 ,'10005')
insert into tb values(5 ,'10006')
insert into tb values(6 ,'10007')
insert into tb values(7 ,'10008')
insert into tb values(8 ,'10009')
insert into tb values(9 ,'10010')
insert into tb values(10,'10011')
insert into tb values(11,'10012')
goselect no2 ,
max(case no1 when 0 then no else 0 end) no1,
max(case no1 when 0 then card_id else 0 end) card_id1,
max(case no1 when 1 then no else 0 end) no2,
max(case no1 when 1 then card_id else 0 end) card_id2,
max(case no1 when 2 then no else 0 end) no3,
max(case no1 when 2 then card_id else '' end) card_id3
from
(
select (no-1)/5 no1 , no%5 no2 , no , card_id from tb
) t
group by no2
order by case when no2 = 0 then 2 else 1 enddrop table tb/*
no2 no1 card_id1 no2 card_id2 no3 card_id3
----------- ----------- ----------- ----------- ----------- ----------- ----------
1 1 10001 6 10007 11 10012
2 2 10002 7 10008 0
3 3 10004 8 10009 0
4 4 10005 9 10010 0
0 5 10006 10 10011 0 (所影响的行数为 5 行)*/
insert into tb values(1 ,'10001')
insert into tb values(2 ,'10002')
insert into tb values(3 ,'10004')
insert into tb values(4 ,'10005')
insert into tb values(5 ,'10006')
insert into tb values(6 ,'10007')
insert into tb values(7 ,'10008')
insert into tb values(8 ,'10009')
insert into tb values(9 ,'10010')
insert into tb values(10,'10011')
insert into tb values(11,'10012')
go
--静态SQL
select no2 ,
max(case no1 when 0 then no else 0 end) no1,
max(case no1 when 0 then card_id else 0 end) card_id1,
max(case no1 when 1 then no else 0 end) no2,
max(case no1 when 1 then card_id else 0 end) card_id2,
max(case no1 when 2 then no else 0 end) no3,
max(case no1 when 2 then card_id else '' end) card_id3
from
(
select (no-1)/5 no1 , no%5 no2 , no , card_id from tb
) t
group by no2
order by case when no2 = 0 then 2 else 1 end--动态SQL
declare @sql varchar(8000)
set @sql = 'select no2'
select @sql = @sql + ' , max(case no1 when ''' + cast(no1 as varchar) + ''' then no end) [no' + cast(no1 as varchar) + ']'
+ ' , max(case no1 when ''' + cast(no1 as varchar) + ''' then card_id end) [card_id' + cast(no1 as varchar) + ']'
from (select distinct no1 from (select (no-1)/5 no1 , no%5 no2 , no , card_id from tb) t ) as a
set @sql = @sql + ' from (select (no-1)/5 no1 , no%5 no2 , no , card_id from tb) t group by no2 order by case when no2 = 0 then 2 else 1 end'
exec(@sql) /*
no2 no0 card_id0 no1 card_id1 no2 card_id2
----------- ----------- ---------- ----------- ---------- ----------- ----------
1 1 10001 6 10007 11 10012
2 2 10002 7 10008 NULL NULL
3 3 10004 8 10009 NULL NULL
4 4 10005 9 10010 NULL NULL
0 5 10006 10 10011 NULL NULL
*/drop table tb/*
no2 no1 card_id1 no2 card_id2 no3 card_id3
----------- ----------- ----------- ----------- ----------- ----------- ----------
1 1 10001 6 10007 11 10012
2 2 10002 7 10008 0
3 3 10004 8 10009 0
4 4 10005 9 10010 0
0 5 10006 10 10011 0 (所影响的行数为 5 行)*/