时间戳

首页 > 解决方案 > 带有 IN CLAUSE 动态值的 SQL Pivot

sql - 带有 IN CLAUSE 动态值的 SQL Pivot

问题描述

我有以下格式的表格

细节

+-------------------+--------+------------+----------------+-------+
| LOB               | Level  | Year_Month | Classification | Count |
+-------------------+--------+------------+----------------+-------+
| Mergers& Acqu     | Level3 | 2020-05    | Statement      | 356   |
+-------------------+--------+------------+----------------+-------+
| Wealth Management | Level2 | 2020-05    | Lending        | 23    |
+-------------------+--------+------------+----------------+-------+
| Finance           | Level3 | 2020-06    | Statement      | 65    |
+-------------------+--------+------------+----------------+-------+

如何旋转具有计数的 Year_month 列？

预期结果

+-------------------+--------+----------------+---------+---------+
| LOB               | Level  | Classification | 2020-05 | 2020-06 |
+-------------------+--------+----------------+---------+---------+
| Mergers& Acqu     | Level3 | Statement      | 356     | 0       |
+-------------------+--------+----------------+---------+---------+
| Wealth Management | Level2 | Lending        | 23      | 0       |
+-------------------+--------+----------------+---------+---------+
| Finance           | Level3 | Statement      | 0       | 65      |
+-------------------+--------+----------------+---------+---------+

我尝试了以下查询，但没有得到想要的结果

SELECT * FROM DETAILS
PIVOT (SUM(Count) FOR YEAR_MONTH IN (SELECT distinct YEAR_MONTH FROM DETAILS))

如何使 IN CLAUSE 具有动态值而不是预设值？

标签： sqloraclepivot

解决方案

使用替换变量的选项：

SQL> set verify off
SQL> clear columns
columns cleared
SQL> column llist new_value slist
SQL> select listagg(ym, ', ') within group (order by ym) as llist
  2  from (select distinct
  3          chr(39) || year_month ||chr(39) || ' as "' || year_month ||'"' ym
  4        from details
  5      );

LLIST
--------------------------------------------------------------------------------
'2020-05' as "2020-05", '2020-06' as "2020-06"

SQL> select * from
  2  (select lob, clevel, classification, year_month, ccount from details)
  3     pivot (sum(ccount)
  4            for year_month in (&slist));

LOB               CLEVEL CLASSIFIC    2020-05    2020-06
----------------- ------ --------- ---------- ----------
Finance           level3 statement                    65
Wealth Management level2 lending           23
Mergers and Acqu  level3 statement        356

SQL>

它的缺点LISTAGG是，如果' 的结果超过 4000 个字符，它将失败（但是，再次，你将如何处理包含超过 500 个月的结果？）。

推荐阅读