SQL查询从一张表中获取格式化结果

Question

假设，我有一个 3 列 9 行的表，并且使用的是 SQL Server 2008。

我想编写一个 SQL 查询来从原始表中获取前三行数据row 然后从第 4 行到第 6 行获取数据并放入第二行，依此类推。

表名称为“Table1”

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