在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

在内部连接之后，我得到了6行，相同的location_id，相同的location_name但是不同的图像值，每个location_id都有6个相应的图像，就像这个图像一样

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]

但是我想要将图像行转换为列(img1、img2、img3……)因此最终结果表将是[location_id, location_name, img1, img2, img3, img4, img5, img6]

How can I build the result?

我如何构建结果?

1 个解决方案

#1

Assuming you have maximum 6 images only

假设你只有6张图片

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

示例SQL小提琴:http://sqlfiddle.com/ ! 3 / e89d94/2

#1