SQL concatenate strings in single value

Question

I have two tables, users_tbl and images tbl.

Every user can have more than one image.

I'm trying to select all the users and concatenate the user image in a single row

Example :

Users tbl

user_id    user_name
--------------------
 1          david
 2          tarik

images tbl

image_id   user_id   image_url 
-------------------------------
1            1      "image 12312 url"
2            1      "image 123 url"
3            1      "image 313 url"
4            1      "image 212 url"
5            2      "my image url"
5            2      "my image url2"

I need a select statement that returns the following result:

user_id   image_url 
----------------------------------------------------------------------
    1     "image 12312 url ,image 123 url,image 313 url,image 212 url"
    2     "my image url , my image url2"

Possible duplicate of stackoverflow.com/questions/13639262/…. Use the XML PATH method pre-SQL 2017 and STRING_AGG in later versions. — Dan Guzman
– Dan Guzman, Commented May 26, 2019 at 12:52

Gordon Linoff · Accepted Answer · 2019-05-26 15:03:50Z

1

In SQL Server 2012, you would use for xml path:

select u.*,
       stuff( (select ', ' + i.image_url
               from images i
               where i.user_id = u.user_id
               for xml path ('')
              ), 1, 2, ''
            ) as image_urls
from users u;

EDIT:

If you want the comma at the end, leave out the stuff() and use:

select u.*,
       (select i.image_url + ', '
        from images i
        where i.user_id = u.user_id
        for xml path ('')
       ) as image_urls
from users u;

edited May 26, 2019 at 15:03

answered May 26, 2019 at 13:11

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tarik Husin Over a year ago

how can add a comma at the end of the string too ? like this "my image url , my image url2 ,"

Gordon Linoff Over a year ago

@TarikHusin . . . for xml path returns the results as an XML string, which in this case is the string that you want.

Collectives™ on Stack Overflow

SQL concatenate strings in single value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related