主页 / dba / 问题 / 327126
Accepted
BHASKAR LAHARI
Asked: 2023-05-15 19:20:52 +0800 CST

我有列名作为日期的数据，我需要将这些列取消透视到日期列和值中。我怎样才能在 oracle SQL 开发人员中得到这个

  • 772

所以我的输入表是

在此处输入图像描述

这是将日期列转换为输出中的日期和值

在此处输入图像描述

我需要编写什么 oracle SQL 开发人员代码来实现这个输出表。请帮助我并提前致谢。

query

1 个回答

  1. Best Answer

    使用以下测试表和数据：

    create table foo (
KPI varchar(50),
DIMENSION varchar(50),
FILTER varchar(50),
"01/05/2023" integer,
"02/05/2023" integer,
"03/05/2023" integer,
"04/05/2023" integer,
"05/05/2023" integer,
"06/05/2023" integer,
"07/05/2023" integer);

insert into foo values ('Impression', 'Sessions', 'Mobile', 1, null, 0, 2, 3, null, 5);
insert into foo values ('Weather', null, 'Laptop', 0, null, null, 4, 8, 2, null);
insert into foo values ('Organic', 'Season', null, 7, null, 8, 0, 7, 4, null);

    我创建了一个用于交叉连接表 foo 的数据透视表：

    create table pivot (dates date);
insert into pivot (dates) select to_date('01/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('02/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('03/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('04/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('05/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('06/05/2023', 'DD/MM/YYYY') from dual;
insert into pivot (dates) select to_date('07/05/2023', 'DD/MM/YYYY') from dual;

    这是查询：

    select f.kpi, f.dimension, f.filter, p.dates,
       case p.dates
            when to_date('01/05/2023', 'DD/MM/YYYY') then f."01/05/2023"
            when to_date('02/05/2023', 'DD/MM/YYYY') then f."02/05/2023"
            when to_date('03/05/2023', 'DD/MM/YYYY') then f."03/05/2023"
            when to_date('04/05/2023', 'DD/MM/YYYY') then f."04/05/2023"
            when to_date('05/05/2023', 'DD/MM/YYYY') then f."05/05/2023"
            when to_date('06/05/2023', 'DD/MM/YYYY') then f."06/05/2023"
            when to_date('07/05/2023', 'DD/MM/YYYY') then f."07/05/2023"
       end as val
from foo f cross join pivot p
order by f.kpi, p.dates;

    这是结果集：

        kpi     | dimension | filter |        dates         | val
------------+-----------+--------+----------------------+-----
 Impression | Sessions  | Mobile | 2023-05-01T00:00:00Z |   1
 Impression | Sessions  | Mobile | 2023-05-02T00:00:00Z |
 Impression | Sessions  | Mobile | 2023-05-03T00:00:00Z |   0
 Impression | Sessions  | Mobile | 2023-05-04T00:00:00Z |   2
 Impression | Sessions  | Mobile | 2023-05-05T00:00:00Z |   3
 Impression | Sessions  | Mobile | 2023-05-06T00:00:00Z |
 Impression | Sessions  | Mobile | 2023-05-07T00:00:00Z |   5
 Organic    | Season    |        | 2023-05-01T00:00:00Z |   7
 Organic    | Season    |        | 2023-05-02T00:00:00Z |
 Organic    | Season    |        | 2023-05-03T00:00:00Z |   8
 Organic    | Season    |        | 2023-05-04T00:00:00Z |   0
 Organic    | Season    |        | 2023-05-05T00:00:00Z |   7
 Organic    | Season    |        | 2023-05-06T00:00:00Z |   4
 Organic    | Season    |        | 2023-05-07T00:00:00Z |
 Weather    |           | Laptop | 2023-05-01T00:00:00Z |   0
 Weather    |           | Laptop | 2023-05-02T00:00:00Z |
 Weather    |           | Laptop | 2023-05-03T00:00:00Z |
 Weather    |           | Laptop | 2023-05-04T00:00:00Z |   4
 Weather    |           | Laptop | 2023-05-05T00:00:00Z |   8
 Weather    |           | Laptop | 2023-05-06T00:00:00Z |   2
 Weather    |           | Laptop | 2023-05-07T00:00:00Z |
    • 0

相关问题