mssql return single row from one to many tables

Question

We are trying to get result as single row from one to many relation, there are atleast 25000 rows in actor table and atleast 10 images for each,

**Table** ActorTable
ActorId Int
ActorName nvarchar
ActorAge Int

 ----------------------------------------------------------------
 ActorId    ActorName   ActorAge
 ----------------------------------------------------------------
 1          Actor1      34  
 2          Actor2      40
 3          Actor3      25
 4          Actor4      19
 5          Actor5      45
 ----------------------------------------------------------------

**Table** ActorImagesTable
ImgId Int
ActorId Int
ActorImage nvarchar

 ----------------------------------------------------------------
 ImgId      ActorId   ActorImage
 ----------------------------------------------------------------
 1          1         a.jpg
 2          1         b.jpg
 3          2         c.jpg
 4          2         d.jpg
 5          1         e.jpg
 ----------------------------------------------------------------

Want to return ActorId,ActorName, ActorAge, ActorImage

 ----------------------------------------------------------------
 ActorId    ActorName   ActorAge  ActorImage
 ----------------------------------------------------------------
 1          Actor1      34        a.jpg,b.jpg,e.jpg  
 2          Actor2      40        c.jpg,d.jpg  
 3          Actor3      25
 4          Actor4      19
 5          Actor5      45
 ----------------------------------------------------------------

Please help

Regards

What version of SQL Server are you using? – Radu Gheorghiu Jul 02 '14 at 12:40 — Radu Gheorghiu, Jul 02 '14 at 12:40

Nadendla · Answer 1 · 2014-07-02T12:52:39.717

0

n condition if you specify imageid then you will get one record .If you are simply joining using only join condition then you will get more than one record for sure because it is one to many

If you want to change rows to columns i suggest you to use Pivot

edited Jul 02 '14 at 12:52

answered Jul 02 '14 at 12:42

Nadendla

712
2
7
17

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

Ah, there is no GROUP_CONCAT function in SQL Server. You have to use XML PATH workaround. See Concatenate many rows into a single text string?

edited May 23 '17 at 10:32

Community

1
1

answered Jul 02 '14 at 12:48

Andrei Shakh

181
4

mssql return single row from one to many tables

2 Answers2