Teradata 等效于 UNPIVOT 和 SEARCH FOR from SQL SERVER

Question

我正在尝试将以下查询从基于SQL服务器的代码转换为TERADATA代码。

INSERT INTO #Keyword(Keyword,OmnitureHitsID,Hit_Time_GMT)    
            SELECT  Search, @PrvRowIdentity,Hit_Time_GMT     
            FROM    
            (SELECT     
                Evar02_Search     
            , Hit_Time_GMT     
            , Evar11_End_Keyword     
            , Evar14_End_SrchTrmPassed     
            , Post_Evar02     
            , Post_Evar11     
            , Post_Evar14     
            FROM #MaINTable WHERE ID = @i    
            AND Visid_High =@Visid_High ) p    
            UNPIVOT     
            (SEARCH FOR SearchKeyword IN     
                (Evar02_Search     
                , Evar11_End_Keyword     
                , Evar14_End_SrchTrmPassed     
                , Post_Evar02     
                , Post_Evar11     
                , Post_Evar14 )    
            ) AS unpvt;

谁能告诉我如何在取消枢轴后转换零件。实际上，我不确定SEARCH FOR SearchKeyword IN部分的作用。

任何帮助都值得赞赏:)

Answer 1

不幸的是，Teradata 没有UNPIVOT函数，但您可以使用UNION ALL查询来复制它：

INSERT INTO #Keyword(Keyword,OmnitureHitsID,Hit_Time_GMT)    
SELECT  Search, @PrvRowIdentity, Hit_Time_GMT     
FROM    
(
  SELECT Hit_Time_GMT, Evar02_Search as Search, 'Evar02_Search' as col
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
  UNION ALL
  SELECT Hit_Time_GMT, Evar11_End_Keyword as Search, 'Evar11_End_Keyword' as col       
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
  UNION ALL
  SELECT Hit_Time_GMT, Evar14_End_SrchTrmPassed as Search, 'Evar14_End_SrchTrmPassed' as col       
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
  UNION ALL
  SELECT Hit_Time_GMT, Post_Evar02 as Search, 'Post_Evar02' as col     
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
  UNION ALL
  SELECT Hit_Time_GMT, Post_Evar11 as Search, 'Post_Evar11' as col     
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
  UNION ALL
  SELECT Hit_Time_GMT, Post_Evar14 as Search, 'Post_Evar14' as col     
  FROM #MaINTable 
  WHERE ID = @i    
    AND Visid_High =@Visid_High 
) x

当前UNPIVOT查询的Search部分是获取列出的每个SearchKeyword列中的列的值。 因此，这可以通过使用获取每列以下内容的UNION ALL来复制：

• 每个字段的Hit_Time_GMT
• Search每列的值（Evar02_Search、Post_Evar02等）
• UNION ALL中的最后一个字段仅指定值来自哪一列

Answer 2

> Teradata 14.10+ 允许这样做：

select name, subject, marks
FROM td_unpivot
(
    ON Student
    USING 
    VALUE_COLUMNS('Marks')
    UNPIVOT_COLUMN('Subject')
    COLUMN_LIST('Maths', 'Science', 'English')
) u
;