基于以日期为标题的列日

本文介绍了基于以日期为标题的列日的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我从表格中获得了数据，如下所示

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 ║ ╚═════════╩════════════╩═════════════╝

基于START_DATE，是否可以将标题与日期并置为日期?

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

预期输出为

╔═════════╦════════════╦════════╦════════╦═══════════╗ ║ 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 ║ ╚═════════╩════════════╩════════╩════════╩═══════════╝

表结构和数据

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;

推荐答案

并非不使用动态SQL进行查询.

Not without using dynamic SQL to make the query.

但是，如果您愿意对值进行硬编码，那么:

But if you are willing to hardcode the values then:

SQL小提琴

Oracle 11g R2架构设置:

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;

查询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 |