Skip to main content

TERADATA-PIVOT FUNCTION

                                  PIVOT FUNCTION

§   Splits records by data fields (Columns)
§  Converts Rows to Columns or Columns to Rows
§  Converts an input records into multiple output records.
                             
                                     EXAMPLE

Execute the below query for the results.

§  Before pivot query:
           sel cost_cntr_id, DATASET_NBR,cat,resrc_class,fb_january,fb_february,
      fb_march,fb_april,fb_may,fb_june,fb_july,fb_august,fb_september,
      fb_october,fb_november fb_december,full_year,january,february,march,
      april,may,june,july,august,september,october,november,december
      from qa1_EDW_SRC.LWSN_FT_EMP_HEADCNT_DATA_STG where cost_cntr_id='867'
      and  DATASET_NBR=551 and cat='Exempt' and resrc_class='Headcount'

§  After pivot query:
     select  cost_cntr_id,cat,year_nbr, resrc_class,rpt_dt ,mnth,amt,
      case when mnth like 'fb_%'then 'budget' else 'actuals' end as version
      from td_unpivot(on (sel * from qa1_edw_src.lwsn_ft_emp_headcnt_data_stg
      lwsn where cost_cntr_id='867'and  dataset_nbr=551 and cat='exempt'and      
      resrc_class='headcount'  )using value_columns('amt') unpivot_column
      ('mnth')column_list('january', 'february','march' , 'april' ,'may ',
      'june','july','august','september','october','november','december'
      ,'fb_january','fb_february' ,'fb_march','fb_april','fb_may','fb_june'
      ,'fb_july','fb_august','fb_september','fb_october','fb_november',
      'fb_december')column_alias_list('01','02','03','04','05','06','07','08'
      ,'09','10','11','12','fb-01','fb-02','fb-03','fb-04','fb-05'
      ,'fb-06','fb-07','fb-08','fb-09','fb-10','fb-11','fb-12') )as rs_set;

Popular posts from this blog

UNIX - AIR COMMANDS

air object changed: Compares two branches or versions of a single technical repository or two repositories, and identifies which objects have changed.    The command outputs the list of files that have been added, removed, or changed between the source and target as follows: Code (meaning) Description A  (Added) The file exists in the target but not in the source. R  (Removed) The file exists in the source but not in the target. M  (Modified) The file is different in the source and the target. S  (Same) The file is the same in the source and the target. U  (Unknown) The difference could not be determined. Occurs due to permission errors or when an object cannot be checked out. N  (No object) The specified object does not exist in either the source or the target. air tag diff: ...
Read more

AB INITIO -STRING FUNCTION

STRING FUNCTION String _split : Splits the given string by the specified delimiter. Output of this function will be a vector value. String_split_no_empty: This function behaves as same as string_split function. But only difference is, this function will remove empty strings from the result set. String_index: The function returns the index of the first character in the first occurrence of the string.                                    a. Index numbering of the string starts with 1.                  b. If the string length is zero, then the functions returns 1.                  c. If the string is not pres...
Read more

AB INITIO - LOOKUP FUNCTION

                                                LOOKUP FUNCTION   lookup_next() §   Lookup_next will give you the next available match in the Lookup file. §   Lookup_next should be Preceded after successful call to lookup, lookup_count, lookup_match, lookup_nth, lookup_range, or lookup_next itself. §   The lookup_next function returns NULL if you call it more times than the number of matching records in the file, or if the next matching record does not exist. §   Behavior of lookup_next() on different calls                   The next successive matching record after the previous call to lookup or lookup_range .  ...
Read more