基于以日期为标题的列日 [英] Based on Column Day concatenated with Date as Heading
本文介绍了基于以日期为标题的列日的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我从表格中获得了数据,如下所示
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:
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 |
这篇关于基于以日期为标题的列日的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文