在 SQL 中将行显示为列

发布于02月17日

我有一个表，它有以下 struct 和样本数据:

VENDOR	ORDER	DELIVERY_DATE	REMARKS	USER
PEPSI	1122	20-DEC-22	OPENED	John
PEPSI	1122	22-DEC-22	REQUESTED	Martin
PEPSI	1122	26-DEC-22	IN PROCESS	Wyatt
PEPSI	1122	10-JAN-23	DELAYED	Khabib
PEPSI	1122	22-JAN-23	IN TRANSIT	Karen

表可以包含不同的供应商和订单.我需要在一行中显示每个订单和供应商的数据. 就像这样:

VENDOR	ORDER	DELIVERY_DATE_1	REMARKS_1	USER_1	DELIVERY_DATE_2	REMARKS_2	USER_2	DELIVERY_DATE_3	REMARKS_3	USER_3
PEPSI	1122	20-DEC-22	OPENED	John	22-DEC-22	REQUESTED	Martin	26-DEC-22	IN PROCESS	Wyatt

等

我试过Pivot，但它不起作用:

SELECT VENDOR,
         order_number,
        -- delivery_date,
         pickup_date
         reasonf_of_delay,
         user_name
from table
PIVOT
(count(delivery_date)
 FOR order_number
 )

推荐答案

您可以使用以下代码构建类似的内容:

CREATE TABLE VENDOR_ORDERS (
    VENDOR VARCHAR2(50),
    ORDER_NUMBER NUMBER,
    DELIVERY_DATE DATE,
    REMARKS VARCHAR2(50),
    USER_NAME VARCHAR2(50)
);

INSERT INTO VENDOR_ORDERS VALUES ('PEPSI', 1122, TO_DATE('20-DEC-2022', 'DD-MON-YYYY'), 'OPENED', 'John');
INSERT INTO VENDOR_ORDERS VALUES ('PEPSI', 1122, TO_DATE('22-DEC-2022', 'DD-MON-YYYY'), 'REQUESTED', 'Martin');
INSERT INTO VENDOR_ORDERS VALUES ('PEPSI', 1122, TO_DATE('26-DEC-2022', 'DD-MON-YYYY'), 'IN PROCESS', 'Wyatt');
INSERT INTO VENDOR_ORDERS VALUES ('PEPSI', 1122, TO_DATE('10-JAN-2023', 'DD-MON-YYYY'), 'DELAYED', 'Khabib');
INSERT INTO VENDOR_ORDERS VALUES ('PEPSI', 1122, TO_DATE('22-JAN-2023', 'DD-MON-YYYY'), 'IN TRANSIT', 'Karen');

SELECT *
FROM
(
     SELECT VENDOR
           ,ORDER_NUMBER
           ,DELIVERY_DATE
           ,REMARKS
           ,USER_NAME
           ,ROW_NUMBER() OVER (PARTITION BY VENDOR, ORDER_NUMBER ORDER BY DELIVERY_DATE ASC) AS  rn
     FROM VENDOR_ORDERS vo
) DS
PIVOT
(
     MAX(DELIVERY_DATE) AS DT, MAX(REMARKS) AS R, MAX(USER_NAME) AS UN FOR RN IN ('1', '2', '3', '4', '5')
) PVT

enter image description here