sql - SQL returning two rows matching different criteria into separate columns

Question

I'm having trouble adjusting an SQL query where I need to take two rows that match similar criteria and give them each a column. Here is an example of the table I am querying:

SEQNO	PICKSLIPNO	EVENT_TYPE	DATETIME	SUBJECT
20000	10045	H	2021-09-30 8:21	Picking Slip Created
20001	10045	P	2021-09-30 8:22
20002	10045	I	2021-09-30 8:25
20003	10045	H	2021-09-30 11:22	Order delivered

And the result I would like:

PICKSLIPNO	PickingDate	DeliveryDate
10045	2021-09-30 8:21	2021-09-30 11:22
10052	2021-09-30 8:25	2021-09-30 10:32
10122	2021-09-30 7:32	2021-09-30 13:54

I am trying query and return both the datetimes of picking slip creation and order delivery in two separate columns on the same row for each pickingslipno between a certain date range. I've tried the following code which worked perfectly when I only query one PickslipNo. However, as soon as I try querying a wider range I get an error that my subqueries are returning multiple results.

SELECT (
    SELECT DATETIME
    FROM SALESORDHIST
    WHERE SUBJECT LIKE 'Picking Slip Created'
    AND DATETIME >= GETDATE()-5
    AND DATETIME <= GETDATE()
) AS PickingDate,
(
    SELECT DATETIME
    FROM SALESORDHIST
    WHERE SUBJECT LIKE 'Order delivered'
    AND DATETIME >= GETDATE()-5
    AND DATETIME <= GETDATE()
) AS DeliveryDate
FROM SALESORDHIST 

Apologies if this doesn't make sense and I appreciate you taking the time to read this/help out.

标签： sql

Answer 1

First select the data for the order slip create and then left join to the delivery -- like this:

SELECT SLIP.PICKSLIPNO , SLIP.DATETIME AS PickingDate, DEL.DATETIME as DeliveryDate
FROM SALESORDHIST SLIP
LEFT JOIN SALESORDHIST DEL ON SLIP.PICKSLIPNO = DEL.PICKSLIPNO 
                           AND DEL.SUBJECT LIKE 'Order delivered'
WHERE SLIP.SUBJECT LIKE 'Picking Slip Created'