CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Explorer
Explorer

Reputation: 305

Getting vertical normal rows data display to horizontal format

I have a table of data. I need to get the ID(First Column) and the related 2 column data in horizontal format. The maximum no of corresponding LinkID occurrence is 3 for an ID. Therefore we need to start to display the PC related data after 3 columns. I have calculated the 'Total no of occurrence of ID' in the 4th column as TotalnoofoccurenceofID .

My available table is

ID      LinkID      PC      TotalnoofoccurenceofID          

10R46   R*1005      8017    2               
10R46   R*10335     5019    2               
100R91  R*1005      8017    1               
10R91   R*243       8870    1               
10M95   R*4918      8305    3               
10M95   R*9017      8305    3               
10M95   R*9470      8221    3

I would like to get the result set like this :

ID      TotalnoofoccurenceofID  LinkID1     LinkID2     LinkID3 PC1     PC2     PC3                             
10R46   2                       R*1005      R*10335             8017    5019    
100R91  1                       R*1005                          8017        
10R91   1                       R*243                           8870        
10M95   3                       R*4918      R*9017      R*9470  8305    8305    8221

If I describe the result set then, 

First row  :ID 10R46 and related  2  LinkID(under LinkID1 and LinkID2) and related 2 PCs(under PC1 and PC2)

Second row :ID 100R91 and related 1  LinkID(under LinkID1) and related 1 PC(under PC1)

Third row  :ID 10R91 and related  1  LinkID(under LinkID1)  and related 1 PC(under PC1)

Fourth row :ID 10M95 and related  3  LinkID(under LinkID1,LinkID2,LinkID3) and related 3 PC(under PC1,PC2,PC3)

Please note the maximum LinkID occurrence is 3 and the PC column start after 3 LinkID columns.

Thank you so much.

Upvotes: 0

Views: 1069

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Check this,

Declare @t table(ID varchar(50),LinkID varchar(50),PC int, TotalnoofoccurenceofID int)             
insert into @t
select '10R46',   'R*1005',  8017,    2 union all            
select '10R46',   'R*10335', 5019,    2 union all               
select '100R91',  'R*1005',  8017,    1 union all               
select '10R91',   'R*243',   8870,    1 union all               
select '10M95',   'R*4918',  8305,     3 union all               
select '10M95',   'R*9017',  8305,    3 union all               
select '10M95',   'R*9470',  8221,    3 

;with cte as
(select *,ROW_NUMBER()over(partition by id order by id)rn from @t)

select a.ID,b.LinkID,c.LinkID,d.LinkID,b.PC,c.PC,d.PC from cte a
left join cte b on a.id=b.id and b.rn=1
left join cte c on a.id=c.id and c.rn=2
left join cte d on a.id=d.id and d.rn=3
where a.rn=1

Upvotes: 1

Related Questions