Convert multiple rows to single row on comma separated column

Question

I have a simple query that returns 2 rows as it is joining 2 tables. The query is this:

SELECT 
    id, fecha, tipo, fuente, origen, estado, gravedad, cliente, 
    sectores, datos, idInforme, idDepartamento
FROM 
    Informe I
INNER JOIN 
    InformeDepartamento ID ON I.ID = ID.idInforme

What I need is only to return a single row with the 'idDepartamento' column merged and separated by commas. So the content is 1,4

How can I accomplish this? I've tried with PIVOT but it did not work as I think it is not the right approach for this.

http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — ScaisEdge, Apr 09 '17 at 19:20

score 1 · Accepted Answer · answered Apr 09 '17 at 19:30

1

You can use STUFF and XML path to get what you need. Here is a good example of how to use it: stuff example

answered Apr 09 '17 at 19:30

Thinster

46
3

score 1 · Answer 2 · answered Apr 09 '17 at 19:32

1

STUFF function and XMLPATH made the work.

SELECT id, 
    fecha, 
    tipo, 
    fuente, 
    origen, 
    estado, 
    gravedad, 
    cliente, 
    sectores, 
    datos, 
    idInforme, 
    STUFF((SELECT ',' + CAST(idDepartamento AS VARCHAR(5)) FROM InformeDepartamento WHERE idInforme = I.id FOR XML PATH('')),1,1,'') [PRUEBA]
FROM Informe I
INNER JOIN InformeDepartamento ID ON I.ID = ID.idInforme
GROUP BY id, fecha, tipo, fuente, origen, estado, gravedad, cliente, sectores, datos, idInforme

answered Apr 09 '17 at 19:32

NicoRiff

4,803
3
25
54

i appericiate that you tried of your own.But including so many column in group by often result in wrong result and poor perfromance.http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – KumarHarsh Apr 10 '17 at 05:12

Convert multiple rows to single row on comma separated column

2 Answers2