Teradata Equivalent for UNPIVOT and SEARCH FOR from SQL SERVER

Question

I am trying to convert the following query from SQL server based code to TERADATA code.

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;

Can anyone tell me how to convert the part after UNPIVOT. Actually I am not sure what the SEARCH FOR SearchKeyword IN part does.

Any help is appreciated :)

Answer 1

Unfortunately, Teradata does not have an UNPIVOT function but you can replicate it using a UNION ALL query:

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

The Search part of your current UNPIVOT query is getting the value for the columns in each of the SearchKeyword columns listed. So this can be replicated by using a UNION ALL that gets the following for each column:

• Hit_Time_GMT for each field
• Search value for each of the columns (Evar02_Search, Post_Evar02, etc)
• the last field in the UNION ALL just specifies which column the value came from