Concatenar Subconsultas en una Sola Columna con SQL Server

En SQL Server puedes usar la cláusula FOR XML para concatenar varios registros en una sola columna, separados por un carácter (por ejemplo, una coma).
Esto es útil cuando necesitas mostrar múltiples valores como una lista dentro de una celda.

📖 Artículo recomendado sobre el tema

---

🔹 Ejemplo básico

Supongamos que tienes una tabla Customers con los campos CustomerID y CustomerName.
Queremos concatenar todos los nombres de clientes en una sola columna separados por comas.

SELECT STUFF((
    SELECT ',' + CustomerName
    FROM Customers
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '') AS ConcatenatedNames

👉 Resultado: una columna única llamada ConcatenatedNames con todos los nombres separados por comas.

---

🔹 Desglose de la consulta

1. Subconsulta interna:

   SELECT ',' + CustomerName FROM Customers

   → Devuelve los nombres separados por comas.

2. FOR XML PATH(''):

   SELECT ',' + CustomerName FROM Customers
   FOR XML PATH(''), TYPE

   → Combina todos los valores en una sola fila, sin etiquetas XML.

3. .value('.', 'nvarchar(max)'):

   SELECT ',' + CustomerName FROM Customers
   FOR XML PATH(''), TYPE
   .value('.', 'nvarchar(max)')

   → Convierte el resultado en una cadena de texto.

4. STUFF:

   SELECT ',' + CustomerName FROM Customers
   FOR XML PATH(''), TYPE
   .value('.', 'nvarchar(max)')

   → Elimina la primera coma innecesaria al inicio de la cadena.

5. Alias AS ConcatenatedNames:

   SELECT ',' + CustomerName FROM Customers
   FOR XML PATH(''), TYPE
   .value('.', 'nvarchar(max)')

   → Asigna nombre a la columna resultante.

---

🔹 Ejemplo aplicado: Roles y Permisos

En este ejemplo se concatenan permisos asociados a cada rol, generando una lista en la misma celda:

SELECT DISTINCT
    r.nombre_rol AS [Nombre del rol],
    r.desc_rol AS [Descripción rol],

    -- Subconsulta que genera la lista de permisos concatenados
    (SELECT STUFF((
        SELECT ', ' + nombre
        FROM Permisos
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 1, '')) AS [Permisos]

FROM Permisos_roles AS pr
INNER JOIN Roles AS r ON r.ID = pr.ID_rol
INNER JOIN Permisos AS p ON p.ID = pr.ID_Permiso

👉 Esto devuelve una lista de roles con su descripción y todos los permisos concatenados en la columna Permisos.

---

🔹 Resultado esperado