开发者

T SQL - Conconate Column Multiple Times

开发者 https://www.devze.com 2023-03-02 10:17 出处:网络
So I have the following table: ID|Product_Image 300|/300-01.jpg 300|/300-02.jpg 301|/301.jpg 302|/302.jpg

So I have the following table:

ID | Product_Image

300 | /300-01.jpg

300 | /300-02.jpg

301 | /301.jpg

302 | /302.jpg

There could be an unlimited number of images per ID. I need to concatenate all the image references into one 开发者_StackOverflow中文版column, and I am having trouble generating the following output:

ID | Product Images

300 | /300-01.jpg; /300-02.jpg;

301 | /301.jpg;


-- cte with test data
;with T (ID, Product_Image) as
(
select 300, '/300-01.jpg' union all
select 300, '/300-02.jpg' union all
select 301, '/301.jpg' union all
select 302, '/302.jpg'
)

select
  T.ID,
  (select T2.Product_Image+'; '
   from T as T2 
   where T.ID = T2.ID
   for xml path(''), type).value('.[1]', 'nvarchar(max)') as Product_Images
from T
group by T.ID

Result:

ID   Product_Images
---- -------------------------
300  /300-01.jpg; /300-02.jpg; 
301  /301.jpg; 
302  /302.jpg; 
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号