将 SQL Server UNPIVOT 转换为 TERADATA

Question

我正在尝试使用UNPIVOT逻辑将以下代码从SQL Server转换为Teradata：

insert  into 
IPData
  select
  1,
ProductCode || '|' ||
LocationCode || '|' ||
TimeCode || '|' ||
MeasureCode || '|' ||
cast(MeasureVal as varchar(12))
from
(
 select
    p.SubsectionCode ProductCode,
    td_ss.LocationCode,
    td_ss.TimeCode,
    sum(
  case  
            when td_ss.Life = 'C' 
and td_ss.Seasonality = 'AW' then td_ss.DepotStockRetail
            else 0
        end) StkDep_AW_Act,
    sum(
 case   
            when td_ss.Life = 'C' 
and td_ss.Seasonality = 'C' then td_ss.DepotStockRetail
            else 0
        end) StkDep_Cnt_Act,
    sum(
 case   
            when td_ss.Life = 'C' 
and td_ss.Seasonality = 'SS' then td_ss.DepotStockRetail
            else 0
        end) StkDep_SS_Act,
    sum(
  case  td_ss.Life
            when 'T' then td_ss.DepotStockRetail
            else 0
        end) StkDep_Trm_Act,
    sum(td_ss.DepotStockRetail) StkDep_Tot_Act
from
    Stock td_ss
inner join
    Product p
on  td_ss.Kimball = p.CurrentKimball
and td_ss.SectArea = p.SectArea
and td_ss.SeasonID = p.SeasonID
group by
    p.SubsectionCode,
        td_ss.LocationCode,
        td_ss.TimeCode)x
UNPIVOT
    (MeasureVal for MeasureCode in
        (StkDep_AW_Act, StkDep_Cnt_Act, StkDep_SS_Act, StkDep_Trm_Act, StkDep_Tot_Act)) as unpvt
where
    MeasureVal <> 0;

我正在为UNPIVOT部分而苦苦挣扎.截至目前，我没有任何表格中的数据，因此非常感谢有关UNPIVOT的任何帮助。

Answer 1

Teradata在TD16中支持UNPIVOT，但语法更接近Oracle而不是SQL Server。

在有TD_UNPIVOT表运算符之前，这应该是正确的翻译：

INSERT  INTO 
IPData
SELECT * FROM TD_UNPIVOT(
ON(
   SELECT
        1,
      ProductCode || '|' ||
      LocationCode || '|' ||
      TimeCode || '|' ||
      MeasureCode || '|' ||
      Cast(MeasureVal AS VARCHAR(12))
   FROM
      (
       SELECT
          p.SubsectionCode ProductCode,
          td_ss.LocationCode,
          td_ss.TimeCode,
          Sum(
        CASE  
                  WHEN td_ss.Life = 'C' 
      AND td_ss.Seasonality = 'AW' THEN td_ss.DepotStockRetail
                  ELSE 0
              end) StkDep_AW_Act,
          Sum(
       CASE   
                  WHEN td_ss.Life = 'C' 
      AND td_ss.Seasonality = 'C' THEN td_ss.DepotStockRetail
                  ELSE 0
              end) StkDep_Cnt_Act,
          Sum(
       CASE   
                  WHEN td_ss.Life = 'C' 
      AND td_ss.Seasonality = 'SS' THEN td_ss.DepotStockRetail
                  ELSE 0
              end) StkDep_SS_Act,
          Sum(
        CASE  td_ss.Life
                  WHEN 'T' THEN td_ss.DepotStockRetail
                  ELSE 0
              end) StkDep_Trm_Act,
          Sum(td_ss.DepotStockRetail) StkDep_Tot_Act
      FROM
          Stock td_ss
      INNER JOIN
          Product p
      ON  td_ss.Kimball = p.CurrentKimball
      AND td_ss.SectArea = p.SectArea
      AND td_ss.SeasonID = p.SeasonID
      GROUP BY
          p.SubsectionCode,
              td_ss.LocationCode,
              td_ss.TimeCode)x
   ) AS dt
   USING
   VALUE_COLUMNS('MeasureVal')
   UNPIVOT_COLUMN('Measures')
   COLUMN_LIST('StkDep_AW_Act', 'StkDep_Cnt_Act', 'StkDep_SS_Act', 'StkDep_Trm_Act', 'StkDep_Tot_Act')
) AS unpvt
WHERE
    MeasureVal <> 0;

Answer 2

     insert into IPData
               select
              1,
              ProductCode || '|' ||
              LocationCode || '|' ||
        TimeCode || '|' ||
        MeasureCode || '|' ||
        cast(MeasureVal as varchar(12))
     from
     (Select
            p.SubsectionCode ProductCode,
            td_ss.LocationCode,
            td_ss.TimeCode,
            sum(case 
                    when td_ss.Life = 'C' and td_ss.Seasonality = 'AW' then td_ss.DepotStockRetail
                    else 0
                end) MeasureVal,'StkDep_AW_Act' AS MeasureCode
        FROM    Stock td_ss
        inner join
            Product p
        on  td_ss.Kimball = p.CurrentKimball
        and td_ss.SectArea = p.SectArea
        and td_ss.SeasonID = p.SeasonID
        group by
            p.SubsectionCode,
            td_ss.LocationCode,
            td_ss.TimeCode
                having
        MeasureVal <> 0
     UNION ALL
        Select
            p.SubsectionCode ProductCode,
            td_ss.LocationCode,
            td_ss.TimeCode,
            sum(case 
                    when td_ss.Life = 'C' and td_ss.Seasonality = 'C' then td_ss.DepotStockRetail
                    else 0
                end) MeasureVal, 'StkDep_Cnt_Act' AS MeasureCode
        FROM    Stock td_ss
        inner join
            Product p
        on  td_ss.Kimball = p.CurrentKimball
        and td_ss.SectArea = p.SectArea
        and td_ss.SeasonID = p.SeasonID
        group by
            p.SubsectionCode,
            td_ss.LocationCode,
            td_ss.TimeCode
                having
        MeasureVal <> 0
     UNION ALL
        Select
            p.SubsectionCode ProductCode,
            td_ss.LocationCode,
            td_ss.TimeCode,
            sum(case 
                    when td_ss.Life = 'C' and td_ss.Seasonality = 'SS' then td_ss.DepotStockRetail
                    else 0
                end) MeasureVal, 'StkDep_SS_Act' AS MeasureCode
        FROM    Stock td_ss
        inner join
            Product p
        on  td_ss.Kimball = p.CurrentKimball
        and td_ss.SectArea = p.SectArea
        and td_ss.SeasonID = p.SeasonID
        group by
            p.SubsectionCode,
            td_ss.LocationCode,
            td_ss.TimeCode
                having
        MeasureVal <> 0
     UNION ALL
        Select
            p.SubsectionCode ProductCode,
            td_ss.LocationCode,
            td_ss.TimeCode,
            sum(case td_ss.Life
                    when 'T' then td_ss.DepotStockRetail
                    else 0
                end) MeasureVal, 'StkDep_Trm_Act' AS MeasureCode
        FROM    Stock td_ss
        inner join
            Product p
        on  td_ss.Kimball = p.CurrentKimball
        and td_ss.SectArea = p.SectArea
        and td_ss.SeasonID = p.SeasonID
        group by
            p.SubsectionCode,
            td_ss.LocationCode,
            td_ss.TimeCode
                having
        MeasureVal <> 0
     UNION ALL
        Select
            p.SubsectionCode ProductCode,
            td_ss.LocationCode,
            td_ss.TimeCode,
            sum(td_ss.DepotStockRetail) MeasureVal, 'StkDep_Tot_Act' AS MeasureCode
        FROM    Stock td_ss
        inner join
            Product p
        on  td_ss.Kimball = p.CurrentKimball
        and td_ss.SectArea = p.SectArea
        and td_ss.SeasonID = p.SeasonID
        group by
            p.SubsectionCode,
            td_ss.LocationCode,
            td_ss.TimeCode  
                having
        MeasureVal <> 0
     )x;