Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Jun 2006 @ 13:26:38 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: SQL to Normalize Columns to Rows
 
From:   Henderson, John

You might also consider building a solution that uses the DBC Catalog and the power of Teradata to create the insert scripts for you, since there may be other tables and requirements to perform these types of table conversion. Please understand that the majority of the work I do is with Teradata using batch mainframe jobs, so the following may have to be tweaked depending on how you access Teradata.

Job step one - create a control member to read the DBC.TABLES catalog table to generate the "mass" of insert statements based on the table into another control member:

     .EXPORT REPORT DDNAME=CTL001;
     .FORMAT OFF;
     .SET WIDTH 72;
     .SET FOLDLINE ON ALL;

     SELECT 'INSERT INTO [New DBS].[New TBL]'                    (TITLE'');
     SELECT ';SELECT CUSTOMER_NUMBER'                            (TITLE'')
          , '      , ' || CSUM(1,A.COLUMNNAME) || ' AS COL_TYPE' (TITLE'')

          , '      , ' ||A.COLUMNNAME || ' AS COL_DATA'          (TITLE'')

          , '   FROM [OLD DBS].[OLD TBL]'                        (TITLE'')
          , '  WHERE ' || TRIM(A.COLUMNNAME) || ' IS NOT NULL'   (TITLE'')
       FROM DBC.COLUMNS A
      WHERE DATABASENAME = 'ACPD0848'
          AND TABLENAME = 'JBH_EMPLOYEE_MANUAL2'

       ORDER BY COLUMNNAME
     ;
     SELECT ';'                                                  (TITLE'');

     .EXPORT RESET;

NOTE: If the last positions of the old tables column name is the value that needs to be inserted for COL_TYPE, then replace the CSUM function with something like SUBSTR(A.COLUMNNAME,4,3).

Job step two - execute BTQMAIN using the created control card from job step one - assuming you've already created the new database and table to be insert to.

Rinse and repeat for each table to be converted.


Thanks,

John



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023