Based on Column Day concatenated with Date as Heading

Question

I have the data from my table as shown below

╔═════════╦════════════╦═════════════╗
║ PROD_ID ║ START_DATE ║ TOT_HOURS   ║
╠═════════╬════════════╬═════════════╣
║ PR220   ║ 19-Sep-17  ║ 0           ║
║ PR2230  ║ 19-Sep-17  ║ 2           ║
║ PR9702  ║ 19-Sep-17  ║ 3           ║
║ PR9036  ║ 19-Sep-17  ║ 0.6         ║
║ PR9036  ║ 18-Sep-17  ║ 3.4         ║
║ PR9609  ║ 18-Sep-17  ║ 5           ║
║ PR91034 ║ 18-Sep-17  ║ 4           ║
║ PR7127  ║ 18-Sep-17  ║ 0           ║
╚═════════╩════════════╩═════════════╝

Based on the START_DATE, could it be possible to have headings with Day concatenated with Date?

Expected output is

╔═════════╦════════════╦════════╦════════╦═══════════╗
║ PROD_ID ║ START_DATE ║ MON-18 ║ TUE-19 ║ TOT_HOURS ║
╠═════════╬════════════╬════════╬════════╬═══════════╣
║ PR220   ║ 19-Sep-17  ║        ║ 0      ║ 0         ║
║ PR2230  ║ 19-Sep-17  ║        ║ 2      ║ 2         ║
║ PR9702  ║ 19-Sep-17  ║        ║ 3      ║ 3         ║
║ PR9036  ║ 19-Sep-17  ║        ║ 0.6    ║ 0.6       ║
║ PR9036  ║ 18-Sep-17  ║ 3.4    ║        ║ 3.4       ║
║ PR9609  ║ 18-Sep-17  ║ 5      ║        ║ 5         ║
║ PR91034 ║ 18-Sep-17  ║ 4      ║        ║ 4         ║
║ PR7127  ║ 18-Sep-17  ║ 0      ║        ║ 0         ║
╚═════════╩════════════╩════════╩════════╩═══════════╝

Table structure and data

CREATE TABLE PROD_TIMINGS
(
  PROD_ID     VARCHAR2(12 BYTE),
  START_DATE  DATE,
  TOT_HOURS   NUMBER
);

SET DEFINE OFF;
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR220', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR2230', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9702', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9036', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0.6);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9036', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.4);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9609', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR91034', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR7127', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0);
COMMIT;

Potential duplicate of https://stackoverflow.com/q/15491661/1509264 — MT0, Sep 26 '17 at 12:29
@MT0 Not sure why this is a duplicate of https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql — Jacob, Sep 26 '17 at 12:31
Because you appear to want to generate dynamic columns based on a pivot. — MT0, Sep 26 '17 at 12:34

score 1 · Accepted Answer · answered Sep 26 '17 at 12:22

1

Not without using dynamic SQL to make the query.

But if you are willing to hardcode the values then:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE PROD_TIMINGS( PROD_ID, START_DATE, TOT_HOURS ) AS
SELECT 'PR220',   DATE '2017-09-19', 0 FROM DUAL UNION ALL
SELECT 'PR2230',  DATE '2017-09-19', 2 FROM DUAL UNION ALL
SELECT 'PR9702',  DATE '2017-09-19', 3 FROM DUAL UNION ALL
SELECT 'PR9036',  DATE '2017-09-19', 0.6 FROM DUAL UNION ALL
SELECT 'PR9036',  DATE '2017-09-18', 3.4 FROM DUAL UNION ALL
SELECT 'PR9609',  DATE '2017-09-18', 5 FROM DUAL UNION ALL
SELECT 'PR91034', DATE '2017-09-18', 4 FROM DUAL UNION ALL
SELECT 'PR7127',  DATE '2017-09-18', 0 FROM DUAL;

Query 1:

SELECT PROD_ID,
       START_DATE,
       CASE START_DATE WHEN DATE '2017-09-18' THEN TOT_HOURS END AS "MON-18",
       CASE START_DATE WHEN DATE '2017-09-19' THEN TOT_HOURS END AS "TUE-19",
       TOT_HOURS
FROM   PROD_TIMINGS

Results:

| PROD_ID |           START_DATE | MON-18 | TUE-19 | TOT_HOURS |
|---------|----------------------|--------|--------|-----------|
|   PR220 | 2017-09-19T00:00:00Z | (null) |      0 |         0 |
|  PR2230 | 2017-09-19T00:00:00Z | (null) |      2 |         2 |
|  PR9702 | 2017-09-19T00:00:00Z | (null) |      3 |         3 |
|  PR9036 | 2017-09-19T00:00:00Z | (null) |    0.6 |       0.6 |
|  PR9036 | 2017-09-18T00:00:00Z |    3.4 | (null) |       3.4 |
|  PR9609 | 2017-09-18T00:00:00Z |      5 | (null) |         5 |
| PR91034 | 2017-09-18T00:00:00Z |      4 | (null) |         4 |
|  PR7127 | 2017-09-18T00:00:00Z |      0 | (null) |         0 |

answered Sep 26 '17 at 12:22

MT0

143,790
11
59
117

I have the start date and end date passed as parameters, so having these two dates can any way avoid the hardcoding? – Jacob Sep 26 '17 at 12:26
I have to avoid hardcoding of dates and days. – Jacob Sep 26 '17 at 12:28
You cannot use dynamic values in a pivot or as column aliases - you will either need to use hardcoded values or dynamic SQL to generate the query (with hardcoded values in the generated query). I have linked to a question on dynamic pivoting and if this is what you require then the question should be closed as a duplicate. – MT0 Sep 26 '17 at 12:33
How can I use dynamic SQL generate the query? – Jacob Sep 26 '17 at 12:36
See [this answer](https://stackoverflow.com/a/23698873/1509264) or [this answer](https://stackoverflow.com/a/43569574/1509264) in the question I linked. – MT0 Sep 26 '17 at 12:46
I have another question, is it possible to show the Day as heading based on the parameters Start and End Date? – Jacob Sep 26 '17 at 13:38
You can use a [row generator](https://stackoverflow.com/a/39641871/1509264) to generate all the possible days and then just feed that into your dynamic SQL for the pivot. – MT0 Sep 26 '17 at 14:00
Can you please have a look a [this](https://stackoverflow.com/questions/46441419/dynamic-query-for-pivot-in-clause)? – Jacob Sep 27 '17 at 07:01

Based on Column Day concatenated with Date as Heading

1 Answers1

Linked