sql - Teradata 等同于从 SQL SERVER 进行 UNPIVOT 和 SEARCH FOR

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;

谁能告诉我如何在 UNPIVOT 之后转换零件。实际上我不确定 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 中的最后一个字段只是指定值来自哪一列