时间戳

首页 > 解决方案 > 具有两行到列的数据透视表

sql - 具有两行到列的数据透视表

问题描述

我的桌子有下一个结果：

our_date | number_people
------------------------
23/09/19 |  26
24/09/19 |  26

总是只有两行

我想旋转这个结果并得到这个：

our_date_1   | number_people_1   | our_date_2   | number_people_2
-----------------------------------------------------------------
23/09/19     |  26               | 24/09/19     |   26

得到 number_people_1 和 number_people_2 之间的差异

我尝试：

select *
from table_1
pivot(
    count(number_people)
    for our_date in (:P_TODAY, :P_YESTERDAY)
)

这是我的实际错误：

ORA-56900: la variable de enlace no está soportada en la operación PIVOT|UNPIVOT
56900. 0000 -  "bind variable is not supported inside pivot|unpivot operation"
*Cause:    Attempted to use bind variables inside pivot|unpivot operation.
*Action:   This is not supported.

怎么了？如何在 for 子句中使用动态值？

最好的祝福

标签： sqloraclepivot

解决方案

错误说：

for fecha in (our_date)

不能将our_date（列名）作为值列表；它（列表）必须包含常量，例如

for our_date in (date '2019-09-23', date '2019-09-24')

修复该问题后，查询可能如下所示：

SQL> with table_1 (our_date, number_people) as
  2    (select date '2019-09-23', 26 from dual union all
  3     select date '2019-09-24', 26 from dual
  4    )
  5  select *
  6  from table_1
  7  pivot (max(number_people)
  8         for our_date in (date '2019-09-23', date '2019-09-24')
  9        );

TO_DATE(' 2019-09-23 00:00:00' TO_DATE(' 2019-09-24 00:00:00'
------------------------------ ------------------------------
                            26                             26

SQL>

但是，这并不是你想要的。

如果该表中有 3、4 或更多行怎么办？有可能吗，还是总是只有 2 行？

如果它总是只有 2 行，那么自联接就可以完成这项工作。例如：

SQL> with table_1 (our_date, number_people) as
  2    (select date '2019-09-23', 26 from dual union all
  3     select date '2019-09-24', 22 from dual
  4    ),
  5  temp as
  6    (select our_date, number_people,
  7       row_number() over (order by our_date) rn
  8     from table_1
  9    )
 10  select
 11    a.our_date our_date_1,
 12    a.number_people number_people_1,
 13    --
 14    b.our_date our_date_2,
 15    b.number_people number_people_2
 16  from temp a cross join temp b
 17  where a.rn = 1
 18    and b.rn = 2;

OUR_DATE_1 NUMBER_PEOPLE_1 OUR_DATE_2 NUMBER_PEOPLE_2
---------- --------------- ---------- ---------------
23.09.2019              26 24.09.2019              22

SQL>

推荐阅读