使用一条SQL语句使得每5行作为一条记录插入到另外一张表的5个列中

Question

如题. 
使用一条SQL语句,使得每5行作为另外一个表的5个列插入. 
估计可能需要在SQL语句中做循环. 
感觉好像无解. 
请各位指点. 如果SQL Server无解，那种数据库支持？

Answer 1

如果存在唯一的连续的ID号，用行列转换就能实现，
如果是2005那么row_number可以实现

Answer 2

一条SQL语句?数据结构是什么样?如果记录没什么规律,不用自定义函数,一条SQL在什么数据库都难请给出原标结构和目标表结构以及数据样本

Answer 3

比如仅有一列:
asd
asd
12412
123
123啊师父
士大夫234
wr2143
345saf34523
...

Answer 4

/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/create table tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理,
  cast(avg(result*1.0) as decimal(18,2)) 平均分,
  sum(result) 总分
from tb
group by name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/drop table tb    ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94想变成 
Name       Subject Result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/create table tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name       Subject     Result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
  union all
  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
  union all
  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1

Answer 5

create table ta (id int identity(1,1),asd varchar(16))
insert into ta select 
'asd' union select  
'12412' union select  
'123' union select  
'123啊师父' union select  
'士大夫234' union select  
'wr2143' union select  
'345saf34523'go
select 
col1 = max(case when id%5 = 0 then asd else null end ),
col2 = max(case when id%5 = 1 then asd else null end),
col3 = max(case when id%5 = 2 then asd else null end),
col4 = max(case when id%5 = 3 then asd else null end),
col5 = max(case when id%5 = 4 then asd else null end)
from ta
group by id/5
/*
col1             col2             col3             col4             col5             
---------------- ---------------- ---------------- ---------------- ---------------- 
NULL             123              123啊师父           12412            345saf34523
asd              wr2143           士大夫234           NULL             NULL*/drop table ta

Answer 6

5如果不定时换成动态　SQL，
有了这样的中间结果就可以搞定了吧

Answer 7

楼上的方法很具有普遍意义,但不太符合我的要求: 
源表中无主键,唯一的条件是顺序读取,每5条生成另外一张表的一条记录.
－－－－用2005的row_number()

Answer 8

源表仅有一列: 
asd 
asd 
12412 
123 
123啊师父 
士大夫234 
wr2143 
345saf34523 
...目标表结果:
比如仅有一列: 
asd     ,   asd,       12412,123,123啊师父 
士大夫234,wr2143,345saf34523 ,...
...

Answer 9

create table ta (asd varchar(16))
insert into ta select 
'asd' union select  
'12412' union select  
'123' union select  
'123啊师父' union select  
'士大夫234' union select  
'wr2143' union select  
'345saf34523'go
select 
col1 = max(case when id%5 = 0 then asd else null end ),
col2 = max(case when id%5 = 1 then asd else null end),
col3 = max(case when id%5 = 2 then asd else null end),
col4 = max(case when id%5 = 3 then asd else null end),
col5 = max(case when id%5 = 4 then asd else null end)
from (select id = (select count(1) 
                   from ta 
                   where asd <= b.asd) ,* 
      from ta b) a
group by id/5
/*
col1             col2             col3             col4             col5             
---------------- ---------------- ---------------- ---------------- ---------------- 
NULL             123              123啊师父           12412            345saf34523
asd              wr2143           士大夫234           NULL             NULL*/drop table ta

Answer 10

有Bug源表仅有一列:   
asd   
asd   
12412   
123   
123啊师父   
士大夫234   
wr2143   
345saf34523   
... 目标表结果: 
c1             c2              c3              c4  c5
-------------- ---------- -------------------- --- --------
asd            asd        12412                123 123啊师父   
士大夫234       wr2143     345saf34523          ... 
...

Answer 11

即便有row_number(),也应该在SQL中包含循环,如何在SQL中循环?

Answer 12

前提是使用一条SQL语句,而非存储过程等.

Answer 13

用子查询呀，一条SQL就行了呀，没有2005，没办法写例子

Answer 14

那也就是说,可以使用Oracle中的RowID来实现.可是如何在SQL中循环?不得要领...

Answer 15

什么00010002 的，IDENTITY 知道吗？种子。自然就不会重复。

Answer 16

去加标识列或者用临时表吧, row_number或count计算法出来的行号依赖于排序列,无法得到插入顺序.为什么非要一条语句,本就是不可能实现的事.

Answer 17

happyflystone的方法有广泛性,解答详尽.加35分.
fcuandy的建议有技巧.加5分.

Answer 18

通过happyflystone,fcuandy二位的合力,问题得到解决.
结贴.