Reputation: 476
I have two tables like this:
and
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
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