CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
Robert W. Hunter
Robert W. Hunter

Reputation: 3003

sql server complex join query

I have the following query, it is working but I get 4 columns (3 named the same way) because what I want is just get two columns with the data, "Entidad" and "Servicio" but can't find the way to do it..

SELECT
    Entidades.nombre as entidad,
    Servicios.nombre as servicio,
    sp.nombre        as servicio,
    sc.nombre        as servicio
FROM
    Entidades

LEFT JOIN Banksphere 

    INNER JOIN Servicios
    ON Banksphere.servicio_id = Servicios.id

ON Entidades.id = Banksphere.entidad_id

LEFT JOIN PAS 

     INNER JOIN Servicios sp
     ON sp.id = 3

ON Entidades.id = PAS.entidad_id

LEFT JOIN CAM 

    INNER JOIN Servicios sc
    ON sc.id = 0

ON Entidades.id = CAM.entidad_id
GROUP BY
    Entidades.id, Entidades.nombre, Servicios.nombre, sp.nombre, sc.nombre
ORDER BY
    Entidades.id

The structure I got is this

Entidad     | Servicio | Servicio | Servicio
Corporativo | Abacon   | NULL     | CAM
Corporativo | MCI      | NULL     | CAM
Santander   | Sales    | PAS      | NULL

But I want this...

Entidad     | Servicio
Corporativo | Abacon 
Corporativo | MCI    
Corporativo | CAM
Santander   | Sales   
Santander   | PAS

Upvotes: 0

Views: 1294

Answers (1)

Taryn
Taryn

Reputation: 247810

You should be able to use the UNPIVOT function:

select DISTINCT entidad, value   -- use distinct if you want to remove duplicates
from
(
    SELECT
        Entidades.nombre as entidad,
        Servicios.nombre as servicio1,
        sp.nombre as servicio2,
        sc.nombre as servicio3
    FROM
        Entidades
    LEFT JOIN
        (Banksphere INNER JOIN Servicios
    ON (Banksphere.servicio_id = Servicios.id))
    ON Entidades.id = Banksphere.entidad_id
    LEFT JOIN
        (PAS INNER JOIN Servicios sp
    ON (sp.id = 3))
    ON Entidades.id = PAS.entidad_id
    LEFT JOIN
        (CAM INNER JOIN Servicios sc
    ON (sc.id = 0))
    ON Entidades.id = CAM.entidad_id
    GROUP BY
        Entidades.id, Entidades.nombre, Servicios.nombre, sp.nombre, sc.nombre
) src
unpivot
(
    value
    for col in (servicio1, servicio2, servicio3)
) unpiv

See SQL Fiddle with Demo

Upvotes: 2

Related Questions