How do I pivot my data by date in new columns in SQL Developer?

Question

I have the following data: The type repeats and the pdt repeat but I want to transform this so that the unique dates become columns and the inventory goes underneath the column of the appropriate date.

Pdt Type    Inv Date
Toy BA10010 125 5-Apr-16
Toy BA10020 0   5-Apr-16
Toy BA10030 850 5-Apr-16
Toy BA10040 25  5-Apr-16
Toy BA10050 175 5-Apr-16
Toy BA10060 0   5-Apr-16
Toy BA10070 725 5-Apr-16
Toy BA10080 250 5-Apr-16
Toy BA10090 200 5-Apr-16
Toy BA10100 100 5-Apr-16
Toy BA10110 20  5-Apr-16
Toy BA10120 0   5-Apr-16
Toy BA10130 110 5-Apr-16
Toy BA10140 200 5-Apr-16
Toy BA10010 125 6-Apr-16
Toy BA10020 0   6-Apr-16
Toy BA10030 850 6-Apr-16
Toy BA10040 25  6-Apr-16
Toy BA10050 175 6-Apr-16
Toy BA10060 0   6-Apr-16
Toy BA10070 725 6-Apr-16
Toy BA10080 250 6-Apr-16
Toy BA10090 200 6-Apr-16
Toy BA10100 100 6-Apr-16
Toy BA10110 20  6-Apr-16
Toy BA10120 0   6-Apr-16
Toy BA10130 110 6-Apr-16
Toy BA10140 200 6-Apr-16

I used the following code but it returned a bunch of nulls. I had to use sum because it asked for a function.

select * from
(select pdt, type, Inv, date
from tablename)
Pivot (sum(Inv) for date in ( '05-APR-16','06-APR-16' ));

and it returns

Product Type    Invenory    5-Apr-16    6-Apr-16
Toy BA10010 125 null    null
Toy BA10020 0   null    null
Toy BA10030 850 null    null
Toy BA10040 25  null    null
Toy BA10050 175 null    null
Toy BA10060 0   null    null
Toy BA10070 725 null    null
Toy BA10080 250 null    null
Toy BA10090 200 null    null
Toy BA10100 100 null    null
Toy BA10110 20  null    null
Toy BA10120 0   null    null
Toy BA10130 110 null    null
Toy BA10140 200 null    null
Toy BA10010 125 null    null
Toy BA10020 0   null    null
Toy BA10030 850 null    null
Toy BA10040 25  null    null
Toy BA10050 175 null    null
Toy BA10060 0   null    null
Toy BA10070 725 null    null
Toy BA10080 250 null    null
Toy BA10090 200 null    null
Toy BA10100 100 null    null
Toy BA10110 20  null    null
Toy BA10120 0   null    null
Toy BA10130 110 null    null
Toy BA10140 200 null    null

This will work if you only have two dates. If you want to be able to do this in the future, where the number of columns ( = number of dates) will vary, that's more advanced still - you will need dynamic SQL. If this is for reporting purposes, you are better off using proper reporting tools (instead of writing everything from scratch in SQL or PL/SQL). — , Apr 07 '16 at 00:48

score 1 · Answer 1 · answered Apr 06 '16 at 23:11

You could use conditional aggregation instead:

SQL Fiddle

SELECT
    Pdt,
    Type,
    [5-Apr-16] = SUM(CASE WHEN Date = '20160405' THEN Inv ELSE 0 END),
    [6-Apr-16] = SUM(CASE WHEN Date = '20160406' THEN Inv ELSE 0 END)
FROM tablename
GROUP BY
    Pdt, Type

RESULT:

| Pdt |    Type | 5-Apr-16 | 6-Apr-16 |
|-----|---------|----------|----------|
| Toy | BA10010 |      125 |      125 |
| Toy | BA10020 |        0 |        0 |
| Toy | BA10030 |      850 |      850 |
| Toy | BA10040 |       25 |       25 |
| Toy | BA10050 |      175 |      175 |
| Toy | BA10060 |        0 |        0 |
| Toy | BA10070 |      725 |      725 |
| Toy | BA10080 |      250 |      250 |
| Toy | BA10090 |      200 |      200 |
| Toy | BA10100 |      100 |      100 |
| Toy | BA10110 |       20 |       20 |
| Toy | BA10120 |        0 |        0 |
| Toy | BA10130 |      110 |      110 |
| Toy | BA10140 |      200 |      200 |

`[5-Apr-16] =` is not valid syntax for Oracle. Also, trying to implicitly convert a string literal in `YYYYMMDD` format to a date is probably going to fail (as it is not a typical `NLS_DATE_FORMAT` value); you would need to use `TO_CHAR` for an explicit conversion or use a date literal. — MT0, Mar 15 '21 at 16:17

MT0 · Answer 2 · 2016-04-06T23:26:57.590

Don't use string literals for date values.

You have this in your query:

Pivot (
  sum(Inv) for date in (
    '05-APR-16',
    '06-APR-16'
  )
);

If you convert the strings to date literals then it will work:

PIVOT (
  SUM(INV) FOR "Date" IN (
    DATE '2016-04-05' AS "05-Apr-2016",
    DATE '2016-04-06' AS "06-Apr-2016"
  )
)

Oracle Setup:

CREATE TABLE tablename ( Pdt, Type, Inv, "Date" ) AS
SELECT 'Toy', 'BA10010', 125, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10020', 0  , DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10030', 850, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10040', 25 , DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10050', 175, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10060', 0  , DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10070', 725, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10080', 250, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10090', 200, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10100', 100, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10110', 20 , DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10120', 0  , DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10130', 110, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10140', 200, DATE '2016-04-05' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10010', 125, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10020', 10 , DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10030', 800, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10040', 20 , DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10050', 70,  DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10060', 30 , DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10070', 425, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10080', 150, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10090', 300, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10100', 200, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10110', 30 , DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10120', 10 , DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10130', 210, DATE '2016-04-06' FROM DUAL UNION ALL
SELECT 'Toy', 'BA10140', 300, DATE '2016-04-06' FROM DUAL;

Query:

SELECT *
FROM   (
   SELECT *
   FROM   tablename
)
PIVOT
(
   SUM(INV)
   FOR "Date" IN (
     DATE '2016-04-05' AS "05-Apr-2016",
     DATE '2016-04-06' AS "06-Apr-2016"
   )
)
ORDER BY pdt, type

Output:

PDT TYPE    05-Apr-2016 06-Apr-2016
--- ------- ----------- -----------
Toy BA10010         125         125 
Toy BA10020           0          10 
Toy BA10030         850         800 
Toy BA10040          25          20 
Toy BA10050         175          70 
Toy BA10060           0          30 
Toy BA10070         725         425 
Toy BA10080         250         150 
Toy BA10090         200         300 
Toy BA10100         100         200 
Toy BA10110          20          30 
Toy BA10120           0          10 
Toy BA10130         110         210 
Toy BA10140         200         300

@DataMonger Ask a new question as that is beyond the scope of this one and it is not something that can be answered in comments. — MT0, Mar 15 '21 at 16:18
I got the answer in another post (https://stackoverflow.com/questions/66122520/can-i-pivot-date-rows-into-columns-without-having-to-specify-the-dates-in-the-pi) Thank you for taking the time out to respond. — Data Monger, Mar 15 '21 at 16:27

How do I pivot my data by date in new columns in SQL Developer?

2 Answers2