Hi,

Sample data to play with

Declare @Store table(id int primary key identity(1,1),StoreId int);
Declare @Message table(StoreId int, Messages varchar(100));

Insert into @Store(StoreId)

select 100 union all select 200 union all select 300 union all select 400 union all select 500;

Insert into @Message(StoreId,Messages)

select 100, 'Message1' union all
select 100, 'Message2' union all
select 100, 'Message3' union all
select 100, 'Message4' union all
select 100, 'Message5' union all
select 200, 'Message1' union all
select 200, 'Message2' union all
select 200, 'Message3' union all
select 200, 'Message4' union all
select 200, 'Message5' 

--Required output:

select 100 as StoreId,'Message1' as M1,'Message2' as M2,'Message3'as M3,'Message4' as M4,'Message5' as M5 union all
select 200 as StoreId,'Message1' as M1,'Message2' as M2,'Message3'as M3,'Message4' as M4,'Message5' as M5

How do i achieve this rows to column conversion. Any sample query please

You need to use PIVOT, e.g.

Declare @Store table(id int primary key identity(1,1),StoreId int);
Declare @Message table(StoreId int, Messages varchar(100));

Insert into @Store(StoreId)

select 100 union all select 200 union all select 300 union all select 400 union all select 500;

Insert into @Message(StoreId,Messages)

select 100, 'Message1' union all
select 100, 'Message2' union all
select 100, 'Message3' union all
select 100, 'Message4' union all
select 100, 'Message5' union all
select 200, 'Message1' union all
select 200, 'Message2' union all
select 200, 'Message3' union all
select 200, 'Message4' union all
select 200, 'Message5' 

;with cte as (select StoreId, Messages, 'M' + CAST(ROW_NUMBER() over (partition by StoreId order by Messages) as varchar(10)) as Rn from @Message)

select * from cte PIVOT (max(Messages) for Rn IN ([M1],[M2],[M3],[M4],[M5])) pvt 

Hi Naomi,

thanks for the reply and appreciated. works great

Hi Born2Achieve,

Could you please mark the reply that works as answer? It can give points to the answer contributor, as well as helps someone who has the same question.