teradata：在枢轴中的子句中可以从表中获取数据

Question

我希望从年度数据中提取几个 calender Weeks 。完成此操作后，我想将其旋转，以便每个 ID 都有一行。

我们有一个表 db.my_cws 只有一个列 cw 包含我们感兴趣的 calender Weeks

。 > Calender Weeks 。

CREATE TABLE DB.MY_TABLE  AS
(
    SELECT ID,
    WeekNumber_Of_Year(Sales_Date)) AS CW,
    AVG(Sales) AS Sales
    FROM DB.DataBase_XYZ
    WHERE CW IN (SELECT CW FROM DB.MY_CWs)
    GROUP BY ID,CW
) WITH DATA;

此代码为我们提供了这样的输出：

但是，我想旋转它，以便我得到这样的输出：

我从代码 在此处 并进行以下内容，但是 teradata not响应，也没有错误。

CREATE TABLE DB.MY_TABLE2  AS
(
SELECT *
FROM DB.MY_TABLE
PIVOT
 (SUM(Sales) AS  Sales
  FOR CW IN (SELECT CW FROM DB.MY_CWs)
 ) AS dt 
) WITH DATA;

如果代替（从db.my_cws选择CW）我会使用（15,16,17），那么一切都很好，我会得到枢纽的表格，如上所示。

谁能建议我在哪里犯错？ 非常感谢。

Answer 1

我试图重新创建场景。
我要低于错误。

CREATE TABLE Failed. 4306: (-4306)Invalid PIVOT query: PIVOT query with sub-query in IN-List is not supported in DDL statement.

在枢轴表中使用子查询时几乎没有限制。

TD文档：
https://docs.teradata.com/r/Teradata-VantageTM-NewSQL-Engine-Release-Summary/March-2019/Release-16.20-Feature-Update- 1-features/subquery-support-in-pivot in-list

TD文档的摘要

注意事项

在列表中具有子查询的枢轴在多列表中不支持。枢轴列在优化阶段进行动态决定。由于这种动态行为，以下是枢轴查询的用法考虑因素，并在列表中具有子查询。

• 在DDL创建语句中不支持。
• 在存储过程的光标fetch语句中不支持
• 如果在列表中给出了子查询，则不允许在枢轴查询上进行集合操作。
• 结果枢轴列名不能在选择列表中明确指定。
• 不支持子句。

如果您使用的是SQL Assistant，请检查您的历史记录是否有错误详细信息。
否则，您可以查询dbc.dbqlogtbl检查错误文章。

解决方法：

您可以通过动态SQL和存储过程实现所需的输出。

步骤：

1. 将子查询的输出转换为字符串。我们可以通过xmlagg做到这一点。
2. 加入in in子句中的step1输出并执行动态生成的SQL。

REPLACE PROCEDURE DYNAMIC_PIVOT()
BEGIN
DECLARE Sqltxt VARCHAR(1000);
DECLARE CWtxt VARCHAR(250);

--Convert rows from MY_CWs to comma delimited string
SET CWtxt=(SELECT TRIM(  TRAILING ',' FROM  (  XMLAGG(CAST(CW AS VARCHAR(10))||',') (VARCHAR(255)) )  ) FROM MY_CWs);
SET Sqltxt=('CREATE TABLE MY_TABLE2  AS
(
SELECT *
FROM MY_TABLE
PIVOT
 (SUM(Sales) AS  Sales
  FOR CW IN ('|| CWtxt  ||')
 ) AS dt 
) WITH DATA;') ;

CALL DBC.SYSEXECSQL(Sqltxt);

END;


CALL  DYNAMIC_PIVOT();