SQL Query to get formatted result from one table

Question

Suppose,I have a table with 3 columns and 9 rows and I am using SQL Server 2008.

I want to write a SQL query to get first three rows of data from the original table in one row then from 4th row to 6th row get the data and put in the second row and so forth.

The table name is "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

I am looking for this result after I run the query:(3 rows , 1 column)

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

Thanks in Advance!

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