CODEWITHSUNDEEP
sql-serversql-server-2008
mlg74
mlg74

Reputation: 520

Join table and combine results from one into gridview

I need to join 2 tables, the 1st table will return 1 row for each query, but the 2nd table has 1 or more rows that I want to combine and give a value in the one result of the 1st table. For the below dataset, I want to return only 2 rows, with VideoName 1 and 2, then the personId that each video was sent to. So Video 1 was sent to person 3 and person 4 is the first row. Video 2 was sent only to person 4.

Table 1

VideoId  Videoname  
1        Video1
2        Video2

Table 2

SendId   VideoId  PersonId
1         1         4
2         1         3
3         2         4

Desired output

VideoName    Sent to
 Video1      4 and 3
 Video2      4

Upvotes: 1

Views: 58

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Try this:

select t1.*,
    stuff((select distinct ' and ' + convert(varchar(10),personId) 
            from table2 t2 where t1.videoid = t2.videoId
            for xml path('')),
    1,5,'')
from table1 t1;

For more on STUFF and FOR XML PATH:

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Use For Xml Path with Stuff

SELECT DISTINCT VIDEONAME, STUFF((SELECT ','+CAST(PERSONID AS VARCHAR(30)) FROM TABLE2 T2 WHERE T1.VIDEOID=T2.VIDEOID FOR XML PATH('')
),1,1,'') FROM TABLE1 T1

Upvotes: 1

Related Questions