Display rows as columns in SQL

Question

I have a table which has the following structure and sample data:

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

Table could contain different VENDORs and ORDERs. I need to display in one row data for each order and vendor. Like this:

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

etc

I've tried PIVOT, but it does not work:

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

Does this answer your question? [Using pivot on multiple columns of an Oracle row](https://stackoverflow.com/questions/23939244/using-pivot-on-multiple-columns-of-an-oracle-row) — astentx, Feb 18 '23 at 07:30

score 2 · Accepted Answer · answered Feb 17 '23 at 08:56

You can build something similar with the following:

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

The issue is that the PIVOT columns are static in this way. So, you can add 10 or 20 which is most in your case and have NULL value if such records do not exists.

Mahamoutou · Answer 2 · 2023-02-17T10:31:44.610

The simplest way to do that is to use PIVOT clause as you started to do. But, you need to add as many aggregate measures as you have columns (DELIVERY_DATE, REMARKS, USER#). Also, you need to rank (row_number) all the rows per VENDOR, ORDER# before using PIVOT clause like below. Finally, you can change aliases produced by PIVOT clause for your columns in the upper SELECT.

SELECT *
  FROM (
   SELECT T.*,
    ROW_NUMBER() OVER(PARTITION BY VENDOR, ORDER# ORDER BY DELIVERY_DATE, ROWNUM) RN
   FROM TAB_NAME T
) PIVOT(
  MAX(DELIVERY_DATE) AS DELIVERY_DATE
, MAX(REMARKS) AS REMARKS
, MAX(USER#) AS USER#
   FOR RN IN (1, 2, 3, 4, 5)
);

demo on db<>fiddle

score 0 · Answer 3 · answered Feb 17 '23 at 08:56

Please read the documentation for pivot. For instance: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

If you wish to pivot you must know for which values returned for a column in a row you will have different columns. Using plain SQL and not dynamic SQL you can't have an undetermined number of columns. I did not really understand what columns your SQL must return.

score 0 · Answer 4 · answered Feb 17 '23 at 09:34

SELECT Vendor, 'Order 1' AS OrderNumber, 
   MAX(CASE WHEN RowNum = 1 THEN Delivery_Date END) AS Delivery_Date_1,
   MAX(CASE WHEN RowNum = 1 THEN Remarks END) AS Remarks_1,
   MAX(CASE WHEN RowNum = 1 THEN User END) AS User_1,
   MAX(CASE WHEN RowNum = 2 THEN Delivery_Date END) AS Delivery_Date_2,
   MAX(CASE WHEN RowNum = 2 THEN Remarks END) AS Remarks_2,
   MAX(CASE WHEN RowNum = 2 THEN User END) AS User_2,
   MAX(CASE WHEN RowNum = 3 THEN Delivery_Date END) AS Delivery_Date_3,
   MAX(CASE WHEN RowNum = 3 THEN Remarks END) AS Remarks_3,
   MAX(CASE WHEN RowNum = 3 THEN User END) AS User_3,
   MAX(CASE WHEN RowNum = 4 THEN Delivery_Date END) AS Delivery_Date_4,
   MAX(CASE WHEN RowNum = 4 THEN Remarks END) AS Remarks_4,
   MAX(CASE WHEN RowNum = 4 THEN User END) AS User_4,
   MAX(CASE WHEN RowNum = 5 THEN Delivery_Date END) AS Delivery_Date_5,
   MAX(CASE WHEN RowNum = 5 THEN Remarks END) AS Remarks_5,
   MAX(CASE WHEN RowNum = 5 THEN User END) AS User_5 FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Vendor, Order ORDER BY Id) AS RowNum FROM [[your_table]]

) t WHERE Order = 1122 GROUP BY Vendor

! Not sure about your use case, but this might work. If you have a lot of data, I would suggest creating a new table and inserting with transformations.

The above is a wrong usage of rownum. Because rownum represents the order in which the row was found in the database and not something you may rely on. — Bogdan Dincescu, Feb 17 '23 at 10:19

Display rows as columns in SQL

4 Answers4