SQL查询来获取格式的结果从一个表

Question

假设，我有3列和9行的表，我使用SQL Server 2008的

我想写一个SQL查询来获取数据的前三行从原表中的一行 行然后从第4行到第6行获取数据并放入第二行等等。

表名是 “表1”

Primary_Key | Name | Age 

++++++++++++++++++++++++++++ 


A |  Kyle | 45 

B |  Rob | 66 

C |  Dhruba | 77 

D |  Susan | 99 

E |  Steve | 100 

F |  Mili | 34 

G |  Grover | 54 

H  |  Alan | 76 

I |  Paul | 16 

我找的这个结果我运行查询后：（3行，1列）提前

ColumnA 


+++++++++++++++ 

A, Kyle, 45, B, Rob,66, C,Dhruba,77 

D,Susan,99, E,Steve , 100,F, Mili, 34 

G ,   Grover , 54,H ,  Alan ,  76,I ,  Paul ,  16 

谢谢！ “

Answer 1

declare @T table 
(
    Primary_Key char(1), 
    Name varchar(10), 
    Age int 
) 

insert into @T values 
('A' ,  'Kyle' , 45), 
('B' ,  'Rob' , 66), 
('C' ,  'Dhruba' , 77), 
('D' ,  'Susan' , 99), 
('E' ,  'Steve' , 100), 
('F' ,  'Mili' , 34), 
('G' ,  'Grover' , 54), 
('H' ,  'Alan' , 76), 
('I' ,  'Paul' , 16) 

;with C as 
(
    select *, 
     (row_number() over(order by Primary_Key) - 1)/3 as rn 
    from @T 
) 
select stuff((select ', '+C2.Primary_Key+', '+C2.Name+', '+cast(C2.Age as varchar(10)) 
       from C as C2 
       where C1.rn = C2.rn 
       for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as ColumnA 

from C as C1 
group by C1.rn 
order by C1.rn