CODEWITHSUNDEEP
sql-serverxmlsql-server-2012
Thufir Hawat
Thufir Hawat

Reputation: 476

join many rows into one xml column

I have two tables like this:

enter image description here

and

enter image description here

The first generated table lists all the actors/directors/other and the second table lists all the movies. What I need to do is fit all the Person_ID and Name into one column in the second table, ideally through a join and XML conversion. I know how to convert a whole table to an XML, but I am not sure how to do it to a dozen rows on the fly, is iteration the way? Any ideas?

Upvotes: 0

Views: 434

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

If you use FOR XML PATH('') it eliminates the root tags. If you do not specify a column alias, it will just output the value. So you can create a string of values in a sub-query as one of the column values. Something like this should get you started:

create table #Cast (FilmID int, Person_ID int, PersonName varchar(20))
create table #Films (FilmID int , FilmName varchar(20))

insert into #Cast values (1, 1, 'bob')
insert into #Cast values (1, 2, 'tom')
insert into #Cast values (2, 3, 'sam')
insert into #Cast values (2, 4, 'ray')

insert into #Films values (1, 'Flowers for Charlie')
insert into #Films values (2, 'Batman')

SELECT #Films.*,
    SUBSTRING(
        (SELECT ( ',' + convert(varchar(10),Person_ID) + ',' + PersonName)
        FROM #Cast
        WHERE #Films.FilmID = #Cast.FilmID  
        ORDER BY #Films.FilmID, #Cast.FilmID
        FOR XML PATH('')
        ), 2, 8000) CastList
FROM #Films

Upvotes: 1

Related Questions