求一个行列转换的sql语句

Question

比如我用一个sql查询到的结果是：
a                c
========================
1                a1
1                a2
1                a3
1                a4
1                a5
1                a6
1                a7
1                a8
1                a9
1                a10
========================
想把上面的结果合并成：
1               a1 , a2, a3, a4,a5,a6
1               a7 , a8, a9, a10就是按照a这个字段每6条合并
多余6条就得到多一条，以此类推……
最好用一条sql完成（包括前面的sql）

Answer 1

--以下在sql server 2005中实现.
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
goSELECT a,c FROM(SELECT DISTINCT a,px FROM (select a,c,(px-1)/6 px from (select * , row_number() OVER(ORDER BY a , c) px from tb where a = 1) t)t1)A OUTER APPLY(
        SELECT [c]= STUFF(REPLACE(REPLACE(
            (
                SELECT c FROM (select a,c,(px-1)/6 px from (select * , row_number() OVER(ORDER BY a , c) px from tb where a = 1) t) N
                WHERE a = A.a and px = a.px
                FOR XML AUTO
            ), '<N c="', ','), '"/>', ''), 1, 1, '')
)Ndrop table tb/*
a           c
----------- ------------------
1           a1,a10,a2,a3,a4,a5
1           a6,a7,a8,a9(2 行受影响)
*/

Answer 2

--以下在sql server 2000中用函数实现
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
go
--创建一个合并的函数
create function f_hb(@a int , @px int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(c as varchar) from 
  (
    select px = (px-1)/6,a,c from (select px = (select count(1) from (select * from tb where a = 1) t1 where c < t2.c) + 1 , * from (select * from tb where a = 1) t2) t
  ) m where a = @a and px = @px
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select a,c from
(
  select distinct a,px ,dbo.f_hb(a,px) as c from (select px = (px-1)/6,a,c from (select px = (select count(1) from (select * from tb where a = 1) t1 where c < t2.c) + 1 , * from (select * from tb where a = 1) t2) t)m
) ndrop table tb
drop function f_hb/*
a           c
----------- -------------------
1           a1,a2,a3,a4,a5,a10
1           a6,a7,a8,a9(2 行受影响)
*/

Answer 3

create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
go
--创建一个合并的函数
create function f_hb(@a int )
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + c from 
  tb where a = @a 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
goselect a,dbo.f_hb(a) as col
from tb
group by adrop table tb
drop function f_hb
/*
a           col                     
----------- -----------------------------
1           a1,a2,a3,a4,a5,a6,a7,a8,a9,a10
2           a11（所影响的行数为 2 行）
*/

Answer 4

--借用楼上的列子： 
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') goselect a,
[Col1]=max(case when ID=1 then c else '' end ),
[Col2]=max(case when ID=2 then c else '' end ),
[Col3]=max(case when ID=3 then c else '' end ),
[Col4]=max(case when ID=4 then c else '' end ),
[Col5]=max(case when ID=5 then c else '' end ),
[Col6]=max(case when ID=0 then c else '' end )
from (select *,stuff(c,1,1,'')%6 as ID,[group]=(stuff(c,1,1,'')-1)/6  from  tb)T group by a,[group]
a           Col1       Col2       Col3       Col4       Col5       Col6       
----------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           a1         a2         a3         a4         a5         a6
1           a7         a8         a9         a10                   （所影响的行数为 2 行）

Answer 5

2005可这样
select a,
[Col1]=max(case when ID=1 then c else '' end ),
[Col2]=max(case when ID=2 then c else '' end ),
[Col3]=max(case when ID=3 then c else '' end ),
[Col4]=max(case when ID=4 then c else '' end ),
[Col5]=max(case when ID=5 then c else '' end ),
[Col6]=max(case when ID=0 then c else '' end )
from 
(select *,
row_number()over(partition by a order by a)%6 as ID,
[group]=(row_number()over(partition by a order by a)-1)/6 from tb)T 
group by a,[group]

Answer 6

楼上的两个回复，水平高呀。max(case)还是不太明白!!!

Answer 7

借龟哥数据
谢谢啊
if object_id('tb') is not null
  drop table tb
if object_id('f_tb') is not null
  drop function f_tb
go
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(1, 'a11') 
select * from tb
go
create function f_tb(@num int)
returns varchar(200)
as
begin
   declare @s varchar(200)
   select @s=isnull(@s,'')+c+',' from tb where cast(substring(c,2,len(c)-1) as int)>=@num and cast(substring(c,2,len(c)-1) as int)<@num+6
   set @s = substring(@s,1,len(@s)-1)
   return @s
end
goselect a,dbo.f_tb(cast(substring(c,2,len(c)-1) as int)) from tb 
where  (cast(substring(c,2,len(c)-1) as int)-1)%6=0

Answer 8

潇洒老乌龟  好像理解错了
是roy_88的那种结果
谢谢大家了

Answer 9

create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(1, 'a11') 
insert into tb values(2, 'a12') 
insert into tb values(2, 'a13') 
insert into tb values(2, 'a14') 
go
select a,
    [Col1]=max(case when ID=1 then c else '' end ),
    [Col2]=max(case when ID=2 then c else '' end ),
    [Col3]=max(case when ID=3 then c else '' end ),
    [Col4]=max(case when ID=4 then c else '' end ),
    [Col5]=max(case when ID=5 then c else '' end ),
    [Col6]=max(case when ID=0 then c else '' end )
from (select *,stuff(c,1,1,'')%6 as ID,[group]=(stuff(c,1,1,'')-1)/6  from     tb)T group by a,[group]
刚才好好想了想
还是不对啊
比如上面的sql
得到的结果是
a           Col1       Col2       Col3       Col4       Col5       Col6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           a1         a2         a3         a4         a5         a6
1           a7         a8         a9         a10        a11        
2                                                                  a12
2           a13        a14                                         (4 行受影响)而我想要的是
a           Col1       Col2       Col3       Col4       Col5       Col6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           a1         a2         a3         a4         a5         a6
1           a7         a8         a9         a10        a11        
2           a12        a13        a14                                      

Answer 10

还有就是  varchar(10)这个字段要是没有规律的字符如'dfdf','dfd','gege'……
那么stuff这个函数用的就不对了啊

Answer 11

create   table   tb(a   int,   c   varchar(10)) 
insert   into   tb   values(1,   'a1')   
insert   into   tb   values(1,   'a2')   
insert   into   tb   values(1,   'a3')   
insert   into   tb   values(1,   'a4')   
insert   into   tb   values(1,   'a5')   
insert   into   tb   values(1,   'a6')   
insert   into   tb   values(1,   'a7')   
insert   into   tb   values(1,   'a8')   
insert   into   tb   values(1,   'a9')   
insert   into   tb   values(1,   'a10')   
insert   into   tb   values(1,   'a11')   
insert   into   tb   values(2,   'a12')   
insert   into   tb   values(2,   'a13')   
insert   into   tb   values(2,   'a14')   
go select   a, 
        [Col1]=max(case   when   ID=1   then   c   else   ''   end   ), 
        [Col2]=max(case   when   ID=2   then   c   else   ''   end   ), 
        [Col3]=max(case   when   ID=3   then   c   else   ''   end   ), 
        [Col4]=max(case   when   ID=4   then   c   else   ''   end   ), 
        [Col5]=max(case   when   ID=5   then   c   else   ''   end   ), 
        [Col6]=max(case   when   ID=0   then   c   else   ''   end   ) 
from   (select a,c,id%6 as id ,[group] = (id -1)/ 6 
from (
select a,c,id = (select count(1) from tb where aa.a = a and c <= aa.c)
from tb aa) b)T   group   by   a,[group] 
order by a
drop table tb
/*
a           Col1       Col2       Col3       Col4       Col5       Col6       
----------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           a1         a10        a11        a2         a3         a4
1           a5         a6         a7         a8         a9         
2           a12        a13        a14                              （所影响的行数为 3 行）
*/

Answer 12

因为楼主数据的特殊　性，造成col2 --- > a10

Answer 13

create   table   tb(a   int,   c   varchar(10)) 
insert   into   tb   values(1,   'a1')   
insert   into   tb   values(1,   'a2')   
insert   into   tb   values(1,   'a3')   
insert   into   tb   values(1,   'a4')   
insert   into   tb   values(1,   'a5')   
insert   into   tb   values(1,   'a6')   
insert   into   tb   values(1,   'a7')   
insert   into   tb   values(1,   'a8')   
insert   into   tb   values(1,   'a9')   
insert   into   tb   values(1,   'a10')   
insert   into   tb   values(1,   'a11')   
insert   into   tb   values(2,   'a12')   
insert   into   tb   values(2,   'a13')   
insert   into   tb   values(2,   'a14')   
go 
create function ss(@px int,@a int)
returns varchar(100)
as
begin
declare @sql varchar(100) 
select @sql=isnull(@sql+',','')+c from tt where (px-1)/6=(@px-1)/6 and a=@a
return @sql
end
--借用临时表存储数据
select id=identity(int,1,1),* into # from tb
select px=(select count(distinct c) from # where a=a.a and id<a.id)+1,* into tt from # a
select distinct  a,dbo.ss(px,a) from tt

Answer 14

搞了半天终于搞好了
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(1, 'a11') 
insert into tb values(2, 'a12') 
insert into tb values(2, 'a13') 
insert into tb values(2, 'a14') 
go
select a,
    [Col1]=MAX(case when ID%6=1 then c else '' end) ,
    [Col2]=MAX(case when ID%6=2 then c else '' end) ,
    [Col3]=MAX(case when ID%6=3 then c else '' end) ,
    [Col4]=MAX(case when ID%6=4 then c else '' end) ,
    [Col5]=MAX(case when ID%6=5 then c else '' end) ,
    [Col6]=MAX(case when ID%6=0 then c else '' end) 
from 
(select *,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY A) AS ID,
[group]=(ROW_NUMBER() OVER (PARTITION BY A ORDER BY A)-1)/6  
from tb)T 
group by a,[group]
ORDER BY aa           Col1       Col2       Col3       Col4       Col5       Col6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           a1         a2         a3         a4         a5         a6
1           a7         a8         a9         a10        a11        
2           a12        a13        a14                              (3 行受影响)
好像15楼狙击手的sql也对，还没有仔细看呢
17楼的还没有看，因为要一个sql语句
总之，谢谢大家了，学到了不少东西
明天散分……

Answer 15

to： 15 楼 无枪狙击手
你那样写的还是不对的
如果字段c是相同的，即表中存在重复的数据（在项目中这是合理的）
那么就不能得到想要的效果了sql2000中有没有row_number()相似的函数来实现呢
现在上面的只是sql2005解决了
2000的话还是没法解决
求……

Answer 16

--哎,闹了半天是我理解错了,楼主的结果为什么加逗号,这不是害人嘛.
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
goselect a , px2,
  max(case px1 when 0 then c else '' end) 'C1',
  max(case px1 when 1 then c else '' end) 'C2',
  max(case px1 when 2 then c else '' end) 'C3',
  max(case px1 when 3 then c else '' end) 'C4',
  max(case px1 when 4 then c else '' end) 'C5',
  max(case px1 when 5 then c else '' end) 'C6'
from
(
select a,c,(px-1)%6 px1 , (px-1)/6 px2 from
(
 select * , px = (select count(1) from (  select * from tb where a = 1) m where m.a = n.a and m.c < n.c) + 1 from (  select * from tb where a = 1) n
) t
) o
group by a , px2drop table tb/*
a           px2         C1         C2         C3         C4         C5         C6
----------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
1           0           a1         a10        a2         a3         a4         a5
1           1           a6         a7         a8         a9                    (2 行受影响)
*/

Answer 17

--上为2000中的写法,下为2005中的写法
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
goselect a , px2,
  max(case px1 when 0 then c else '' end) 'C1',
  max(case px1 when 1 then c else '' end) 'C2',
  max(case px1 when 2 then c else '' end) 'C3',
  max(case px1 when 3 then c else '' end) 'C4',
  max(case px1 when 4 then c else '' end) 'C5',
  max(case px1 when 5 then c else '' end) 'C6'
from
(
  select a , c , 
  px1 = (row_number() over(order by a , c) - 1)%6,  
  px2 = (row_number() over(order by a , c) - 1)/6  
  from tb where a = 1
) o
group by a , px2drop table tb/*
a           px2         C1         C2         C3         C4         C5         C6
----------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
1           0           a1         a10        a2         a3         a4         a5
1           1           a6         a7         a8         a9                    (2 行受影响)
*/

Answer 18

create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
goselect a ,
  max(case px1 when 0 then c else '' end) 'C1',
  max(case px1 when 1 then c else '' end) 'C2',
  max(case px1 when 2 then c else '' end) 'C3',
  max(case px1 when 3 then c else '' end) 'C4',
  max(case px1 when 4 then c else '' end) 'C5',
  max(case px1 when 5 then c else '' end) 'C6'
from
(
  select a , c , 
  px1 = (row_number() over(order by a , c) - 1)%6,  
  px2 = (row_number() over(order by a , c) - 1)/6  
  from tb where a = 1
) o
group by a , px2drop table tb/*
a           C1         C2         C3         C4         C5         C6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           a1         a10        a2         a3         a4         a5
1           a6         a7         a8         a9                    (2 行受影响)
*/

Answer 19

--2000中的写法:
create table tb(a int, c varchar(10))
insert into tb values(1, 'a1') 
insert into tb values(1, 'a2') 
insert into tb values(1, 'a3') 
insert into tb values(1, 'a4') 
insert into tb values(1, 'a5') 
insert into tb values(1, 'a6') 
insert into tb values(1, 'a7') 
insert into tb values(1, 'a8') 
insert into tb values(1, 'a9') 
insert into tb values(1, 'a10') 
insert into tb values(2, 'a11') 
goselect a ,
  max(case px1 when 0 then c else '' end) 'C1',
  max(case px1 when 1 then c else '' end) 'C2',
  max(case px1 when 2 then c else '' end) 'C3',
  max(case px1 when 3 then c else '' end) 'C4',
  max(case px1 when 4 then c else '' end) 'C5',
  max(case px1 when 5 then c else '' end) 'C6'
from
(
select a,c,(px-1)%6 px1 , (px-1)/6 px2 from
(
 select * , px = (select count(1) from (  select * from tb where a = 1) m where m.a = n.a and m.c < n.c) + 1 from (  select * from tb where a = 1) n
) t
) o
group by a , px2drop table tb/*
a           C1         C2         C3         C4         C5         C6         
----------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           a1         a10        a2         a3         a4         a5
1           a6         a7         a8         a9                    （所影响的行数为 2 行）
*/

Answer 20

潇洒老乌龟 的写法还是有问题
怎么老是理解错我的意思呢
如果有重复行的话就不对了to：无枪狙击手
这个项目烦就烦在只能用一个sql（要传给帐票的）
如果不是的话就不会这么麻烦了-_-!!

Answer 21

to:无枪狙击手
没有标识，一个都没有。2005那个row_number()函数，在2000里面貌似都没有类似的吗？
2000那个是还是有问题，
并且不能用where a = '1' 这种
因为这个表中的数据根本就是不确定的
可能是少数条
可能是多数条
并且字段a也不一定是什么值

Answer 22

to:无枪狙击手 
没有标识，一个都没有。---那也就是C相同的记录完全一样了？？？？

Answer 23

请问这个表还有其它唯一标识某一行的列或组合列吗？ 
如果有，那就能搞定
其实在select的时候加上一个标识id如row_number，就是这个sql的重点
这个写出来了，就好解决了
可是啊……-_-!!
各位高手加油，我已经不行了，呵呵

Answer 24

to:无枪狙击手   
没有标识，一个都没有。 --- 那也就是C相同的记录完全一样了？？？？ 
==================对啊
是不是只能借助临时表了啊
在2000中？

Answer 25

都是牛人   学习ing