Concatenate values resulting from an INNER JOIN in a string separated by commas in SQL Server 2012

Question

Concatenate values resulting from an INNER JOIN in a string separated by commas in SQL Server 2012

Navigation

#1 by (5 votes)

4

I have the following schema in SQL Server 2012:

Tabla 1

Id INT PRIMARY KEY
Descripcion NVARCHAR(20)
Tipo INT

Tabla 2
Id INT PRIMARY KEY
Nombre NVARCHAR(80)
IdTabla1 INT --Llave foranea a la primera tabla

Tabla 3
Id INT PRIMARY KEY
Nombre NVARCHAR(80)
IdTabla1 INT --Llave foranea a la primera tabla

What I need is to formulate a query to have the values of table one and the values of table 2 and table 3 in a field separated by commas (,). Ex.

Tabla 1

Id   |  Descripcion | Tipo

1    |  'Descripcion prueba 1' |  0

2    |  'Descripcion prueba 2' |  2

Table 2

Id   |  Descripcion | IdTabla1

1    |  'ABC' |  1

2    |  'DEF' |  1

3    |  'HIJ' |  2

Table 3

Id   |  Descripcion | IdTabla1

1    |  '123' |  1

2    |  '456' |  2

3    |  '789' |  2

Expected result of the query:

   Id   |  Descripcion | Tipo | ValoresTabla2 | ValoresTabla3

   1  |   'Descripcion prueba 1' | 0 | 'ABC,DEF' | '123'

   2  |   'Descripcion prueba 2' | 2 | 'HIJ' | '456,789'

sql sql-server tsql

asked by AngelRuizC 09.11.2016 в 16:52

source

1 answer

Shape does not apply correctly Validate a textbox in C #

score 5 · Accepted Answer

You can use FOR XML PATH to concatenate the values with a comma and then STUFF to remove the comma further in front.

select t1.id, t1.descripcion, t1.tipo,
       stuff((select ',' + t2.nombre
                from table2 t2
               where t2.IdTabla1 = t1.id
               order by t2.id
                 for xml path('')),1,1,'') as ValoresTabla2,
       stuff((select ',' + t3.nombre
                from table3 t3
               where t3.IdTabla1 = t1.id
               order by t3.id
                 for xml path('')),1,1,'') as ValoresTabla3
  from table1 t1
 order by t1.id;

Demo .