Teradata: IN clause in Pivot can't take data from Table

Question

I wish to extract a few Calender Weeks from an yearly data. Once that's done, I want to pivot it, so that there is one row for each ID.

We have a table DB.MY_CWs having just one column CW containing the Calender Weeks we are interested in.

The following code extracts the relevant 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;

This Code gives us the output like this:

But, I would like to pivot it so that I get an output like this:

I took the help from code here and ran the following, but TeraData doesn't respond and there is no Error either.

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;

If instead of (SELECT CW FROM DB.MY_CWs) I would have used (15,16,17), then everything works fine and I would have got the pivoted Table, as shown above.

Can anyone suggest where I am making the mistake? Many thanks.

Answer 1

I tried to recreate the scenario. I am getting below error.

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

There are few limitation while using subquery in pivot table.

TD Documentation: 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

Snippet from TD Documentation

Considerations

PIVOT with a subquery in the IN-list is not supported in a multistatement request. PIVOT columns are decided dynamically at the optimization phase. Because of this dynamic behavior, the following are usage considerations of a PIVOT query with a subquery in the IN-list.

• Not supported in DDL creation statements.
• Not supported in stored procedure's cursor FETCH statement.
• SET operations are not allowed on a PIVOT query if subquery is given in the IN-list.
• Resultant PIVOT column names cannot be explicitly specified in the SELECT list.
• Does not support ORDER BY clause.

If you are using SQL Assistant, kindly check your history for the error details. Otherwise you can query dbc.dbqlogtbl to check the errortext.

Workaround:

You can achieve the desired output through Dynamic SQL and Stored Procedure.

Steps:

1. Convert the output of the subquery to a String. We can do that through XMLAGG.
2. Concatenate the Step1 output in the IN Clause and execute the dynamically generated 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();