Row concat from this query
- by Álvaro G. Vicario
I have this query:
SELECT DISTINCT IM.EDIFICIOS_ID, TI.TITULAR
FROM IMPORTACION IM
INNER JOIN I_EDIFICIO IE ON IM.IMPORTACION_ID=IE.IMPORTACION_ID
INNER JOIN I_EDIFICIO_TITULAR ET ON IM.IMPORTACION_ID=ET.IMPORTACION_ID AND IE.EDIFICIO_ID=ET.EDIFICIO_ID
INNER JOIN I_TITULAR TI ON IM.IMPORTACION_ID=TI.IMPORTACION_ID AND ET.TITULAR_ID=TI.TITULAR_ID
WHERE TI.TITULAR IS NOT NULL AND TI.TITULAR<>''
ORDER BY IM.EDIFICIOS_ID, TI.TITULAR;
that returns this result set:
EDIFICIOS_ID TITULAR
------------ ------------------
1911 Ana María García
1911 Anselmo Piedrahita
1911 Manuel López
2594 Carlos Pérez
2594 Felisa García
6865 Carlos Pérez
6865 Felisa García
8428 Carlos Pérez
I want to concatenate the values from TITULAR for each EDIFICIOS_ID, so I get this:
EDIFICIOS_ID TITULAR
------------ ------------------
1911 Ana María García; Anselmo Piedrahita; Manuel López
2594 Carlos Pérez; Felisa García
6865 Carlos Pérez; Felisa García
8428 Carlos Pérez
I'm trying to use the FOR XML PATH trick. I've used it in the past but, since I can't really understand how it works, I can't figure out how to apply it to this specific case. Can you provide me with some ideas?