Joining Tables and Show Second Table Data In One Row

Question

In SQL SERVER I need to output row data from a table while joining with another table and column data in second table should be comma separated for each first table row by a single query. If there is no matching rows in second table, It should Display NULL. Following are the tables.

Table 1     Table 2 
ID  Name    Tlb1_ID CITY
1   A       1   ZZ
2   B       1   XX
3   C       2   GG
4   D       2   HH
5   E       3   JJ

I want the output is as follows Result

ID  Name CITY
1   A    ZZ,XX
2   B    GG,HH
3   C    JJ
4   D    NULL
5   E    NULL

You're asking how to concatenate strings. In SQL Server 2017 and later use [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017). There are many duplicates that show how to do the same in previous versions, typically using `FOR XML` — Panagiotis Kanavos, Apr 09 '19 at 11:21
You can use `function` to return value in second table with comma separated. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017 — Ryan Nghiem, Apr 09 '19 at 11:22
@RyanNghiem and what would go into that function? That's what the question is about. — Panagiotis Kanavos, Apr 09 '19 at 11:23

GuidoG · Accepted Answer · 2019-04-09T11:43:50.747

A STUFF could do this, look at this example.

declare @table1 table(ID int, Name varchar(10))
declare @table2 table(ID int, tbl1_ID int, City varchar(10))

insert into @table1 values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')
insert into @table2 values (1, 1, 'ZZ'), (2, 1, 'XX'), (3, 2, 'GG'), (4, 2, 'HH'), (5, 3, 'JJ')

select t1.ID,
       t1.Name,
       stuff(( ( select ', ' + t2.City
                 from   @table2 t2
                 where  t2.tbl1_ID = t1.ID
                 For XML PATH (''))
             ), 1, 2, '') as Citys
from   @table1 t1

the result is

ID  Name    Citys   
--  ----    -----   
1   A       ZZ, XX  
2   B       GG, HH  
3   C       JJ  
4   D       null
5   E       null

score 0 · Answer 2 · answered Apr 09 '19 at 11:39

Try ti use string_Agg:

SELECT 
  tbl_1.Id
, tbl_1.Name
, STRING_AGG(tbl_2.City, ', ') AS Cities
FROM @tbl_1 tbl_1
LEFT JOIN @tbl_2 tbl_2
   ON tbl_1.Id = tbl_2.Tbl1_Id
GROUP BY tbl_1.Id, tbl_1.Name

and sample data:

DECLARE @tbl_1 TABLE
(
   Id int,
   Name varchar(10)
)

DECLARE @tbl_2 TABLE
(
   Tbl1_Id int,
   City varchar(10)
)

INSERT INTO @tbl_1
(
    Id,
    Name
)
VALUES
  (1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D')
, (5, 'E')      

INSERT INTO @tbl_2
(
    Tbl1_Id,
    City
)
VALUES
  ( 1, 'ZZ')
, ( 1, 'XX')
, ( 2, 'GG')
, ( 2, 'HH')
, ( 3, 'JJ')

OUTPUT:

Id  Name    Cities
1   A        ZZ, XX
2   B        GG, HH
3   C        JJ
4   D        NULL
5   E        NULL

Rima · Answer 3 · 2019-04-09T12:32:40.533

0

Subselect will return the comma separated city name for each ID. this is achieved using for xml path and stuff

stuff((SELECT ',' + city FROM #tbl2 where #tbl2.Tlb1_ID = t1.ID FOR XML PATH ('')),1,1,'') CITY

Query

 select  
     t1.*,
     stuff((SELECT ',' + city FROM #tbl2 where #tbl2.Tlb1_ID = t1.ID FOR XML PATH ('')),1,1,'') CITY
      from #tbl1 t1

edited Apr 09 '19 at 12:32

answered Apr 09 '19 at 11:41

Rima

1,447
1
6
12

Please add some explanation to your answer. [From Review](https://stackoverflow.com/review/low-quality-posts/22701746). – Wai Ha Lee Apr 09 '19 at 12:06

Joining Tables and Show Second Table Data In One Row

3 Answers3