CODEWITHSUNDEEP
sqlsql-serverpivot
Mo7ammed
Mo7ammed

Reputation: 51

Convert Rows to columns after inner join in SQL Server

After INNER JOIN I get 6 rows, same location_id, same location_name but different images values, every location_id has 6 corresponding images, as this image

But I want to convert image rows to columns (img1, img2, img3 ... img6) so the final result table will be [location_id, location_name, img1, img2, img3, img4, img5, img6]

How can I build the result?

Upvotes: 2

Views: 2937

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

Assuming you have maximum 6 images only

select 
   location_id, location_name, 
   [1] as img1, 
   [2] as  img2, 
   [3] as  img3,  
   [4] as img4, 
   [5] as  img5, 
   [6] as img6
from
    (
     select 
        location_id, location_name,img_name, 
         row_number() over(partition by location_id order by img_name) rn 
     from innrjointbl
     )s
PIVOT
(MAX(img_name) for rn in ([1],[2],[3],[4],[5],[6])) p

Sample SQL Fiddle: http://sqlfiddle.com/#!3/e89d94/2

Upvotes: 4

Related Questions