CODEWITHSUNDEEP
sqlsql-server-2008
ncoder
ncoder

Reputation: 165

SQL Query to get formatted result from one table

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!

Upvotes: 3

Views: 1209

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Related Questions