SQL Server Group Select in an Only row

owfi6suc 于 2024-01-05 发布在其他

关注(0)|答案(1)|浏览(136)

SELECT 
    loadNumber, 
    CASE 
        WHEN E.Clave = 'LOD' THEN FechaEvento 
        ELSE NULL 
    END AS 'Load Date USA', 
    CASE 
        WHEN E.Clave = 'DAA' THEN FechaEvento 
        ELSE NULL 
    END AS 'Colocacion AA',
    CASE 
        WHEN E.CLAVE = 'RPD' THEN FechaEvento 
        ELSE NULL 
    END AS 'Docs Listos',
    CASE 
        WHEN E.Clave = 'IDT' THEN FechaEvento 
        ELSE NULL 
    END AS 'Llegada MX',
    CASE 
        WHEN E.Clave = 'ULD' THEN FechaEvento 
        ELSE NULL 
    END AS 'Descarga MX'
FROM
    OrdenEvento OE
INNER JOIN 
    Evento E ON OE.idEvento = E.ID
WHERE 
    loadNumber = '60317'
GROUP BY 
    loadNumber, e.Clave, FechaEvento

I have this SQL, trying to gather all in one single row. My goal if there is a number that match with the cases enter and if there is not a single row with full NULL Values, or only a few matches and others with Null Values

loadNumber	Load Date	Colocacion AA	Docs Listos	Llegada MX	Descarga MX
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	2023-12-18 14:21:44.820	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	2023-12-18 14:28:52.877	NULL
60317	2023-12-18 14:24:39.730	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	NULL	NULL	NULL
60317	NULL	NULL	2023-12-18 14:28:02.197	NULL	NULL
60317	NULL	NULL	NULL	NULL	2023-12-18 14:29:04.193

This is the current result and this is the wanted result

loadNumber	Load Date	Colocacion AA	Docs Listos	Llegada MX	Descarga MX
60317	2023-12-18 14:24:39.730	2023-12-18 14:21:44.820	2023-12-18 14:28:02.197	2023-12-18 14:28:52.877	2023-12-18 14:29:04.193

sql-server

来源：https://stackoverflow.com/questions/77681874/group-select-in-an-only-row

1条答案

按热度按时间

vc6uscn91#

You need some aggregation to collapse all those rows to one row per loadNumber.

That also means your GROUP BY should only be loadNumber.

SELECT
  loadNumber, 
  MAX(CASE WHEN E.Clave='LOD' THEN FechaEvento ELSE NULL END)   AS 'Load Date USA', 
  MAX(CASE WHEN E.Clave='DAA' THEN FechaEvento ELSE NULL END)   AS 'Colocacion AA',
  MAX(CASE WHEN E.CLAVE='RPD' THEN FechaEvento ELSE NULL END)   AS 'Docs Listos',
  MAX(CASE WHEN E.Clave='IDT' THEN FechaEvento ELSE NULL END)   AS 'Llegada MX',
  MAX(CASE WHEN E.Clave='ULD' THEN FechaEvento ELSE NULL END)   AS 'Descarga MX'
FROM
  OrdenEvento OE
INNER JOIN
  Evento E
    ON OE.idEvento=E.ID
WHERE
  loadNumber='60317'
GROUP BY
  loadNumber

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server Group Select in an Only row

1条答案

相关问题

热门标签

最新问答