1

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
mlg74
  • 520
  • 1
  • 7
  • 27

2 Answers2

2

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:

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • how do i get the result into the gridview? I tried using "personId" but it said "DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'PersonId'." – mlg74 Jan 08 '17 at 16:20
  • I figured it out from this example and the link you provided. you have to put abc = stuff and "abc" is the value you use in the gridview. thanks! – mlg74 Jan 08 '17 at 16:28
1

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
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • how do i get the result into the gridview? I tried using "personId" but it said "DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'PersonId'." – mlg74 Jan 08 '17 at 16:21
  • 1
    I used abc = stuff and "abc" was the value for gridview – mlg74 Jan 08 '17 at 16:29