Columns to rows

Database
Highlighted
Enthusiast

Columns to rows

I have a table  as below. I have 10- 15 indicator field like aaa_in,bbb_in in source table. How do I change all of the indicator columns to rows?

PROD_CDaaa_inbbb_inccc_inddd_inCountry_CD
ABC123111012345
DEF567101034523
ABC787110098341

 

Sample Output:

PROD_CDMediumCountry_CD
ABC123aaa_in12345
ABC123bbb_in12345
ABC123ccc_in12345
DEF567aaa_in34523
DEF567ccc_in34523
ABC787aaa_in98341
ABC787bbb_in98341

 

 


Accepted Solutions
Teradata Employee

Re: Columns to rows

You can use "unpivot" and then filter. For example, assume we want the unpivoted indicator column values in a column called ind_value (which in this case won't be part of the final result), and "Medium" will contain the source column name for the corresponding ind_value; all other columns of the source table are copied to the unpivoted table. In other words, the unpivoted table "X" will contain rows with (PROD_CD, Country_CD, ind_value, Medium).

 

In TD16+ you can use simplified syntax:

SELECT PROD_CD, Medium, Country_CD
FROM myTable 
UNPIVOT(ind_value FOR Medium IN (aaa_in, bbb_in, ccc_in, ddd_in)) X WHERE ind_value = 1;

In TD14.10 - TD15.10, use table operator syntax (which also still works in TD16+):

SELECT PROD_CD, Medium, Country_CD FROM TD_UNPIVOT(
ON (SELECT * FROM myTable)
USING VALUE_COLUMNS('ind_value')
UNPIVOT_COLUMN('Medium')
COLUMN_LIST('aaa_in','bbb_in','ccc_in','ddd_in')
) X
WHERE ind_value = 1;

 

1 ACCEPTED SOLUTION
7 REPLIES 7
Teradata Employee

Re: Columns to rows

You can use "unpivot" and then filter. For example, assume we want the unpivoted indicator column values in a column called ind_value (which in this case won't be part of the final result), and "Medium" will contain the source column name for the corresponding ind_value; all other columns of the source table are copied to the unpivoted table. In other words, the unpivoted table "X" will contain rows with (PROD_CD, Country_CD, ind_value, Medium).

 

In TD16+ you can use simplified syntax:

SELECT PROD_CD, Medium, Country_CD
FROM myTable 
UNPIVOT(ind_value FOR Medium IN (aaa_in, bbb_in, ccc_in, ddd_in)) X WHERE ind_value = 1;

In TD14.10 - TD15.10, use table operator syntax (which also still works in TD16+):

SELECT PROD_CD, Medium, Country_CD FROM TD_UNPIVOT(
ON (SELECT * FROM myTable)
USING VALUE_COLUMNS('ind_value')
UNPIVOT_COLUMN('Medium')
COLUMN_LIST('aaa_in','bbb_in','ccc_in','ddd_in')
) X
WHERE ind_value = 1;

 

Enthusiast

Re: Columns to rows

Thank you Fred the solution worked. If I have to change the values in Medium column based on the column name. Can I do it within the UNPIVOT function?

 

For example: If column Name is aaa_in then the value should be app, bbb_in is ball and ccc_in = cat

 

PROD_CD

MediumCountry_CD
ABC123App12345
ABC123Ball12345
ABC123Cat12345
DEF567App34523
DEF567Cat34523
ABC787App98341
ABC787Ball98341
Teradata Employee

Re: Columns to rows

Yes, you can supply alternate values for the UNPIVOT column to be used in place of the actual column names.

 

For UNPIVOT:

... FOR Medium IN (aaa_in AS 'App', bbb_in AS 'Ball', ccc_in AS 'Cat') ...

 

For TD_UNPIVOT:

...

COLUMN_LIST('aaa_in','bbb_in','ccc_in')

COLUMN_ALIAS_LIST('App','Ball','Cat')

...

 

 

 

Enthusiast

Re: Columns to rows

Thank you so much,  I also have one more question can we unpivot 2 set of columns? Given a sample input and output. How do I achieve the output?

Sample Input:

PROD_CD

aaa_inbbb_inccc_inddd_inCountry_CDATT_1ATT_1_NMATT_2ATT_2_NMATT_3ATT_3_NM
ABC123111012345120532June4521May??
DEF567101034523730253June??67341August
ABC78711009834183735June3040May??

 

Sample Output:

 

PROD_CD
MediumCountry_CDATT
ABC123aaa_in12345120532
ABC123aaa_in123454521
ABC123bbb_in12345120532
ABC123bbb_in123454521
ABC123ccc_in12345120532
ABC123ccc_in123454521
DEF567aaa_in34523730253
DEF567aaa_in3452367341
DEF567ccc_in34523730253
DEF567ccc_in3452367341
ABC787aaa_in9834183735
ABC787aaa_in983413040
ABC787bbb_in98341 83735
ABC787bbb_in98341 3040
Teradata Employee

Re: Columns to rows

You can nest two successive unpivot operations to obtain that result:

SELECT PROD_CD, Medium, Country_CD, ATT
FROM (SELECT PROD_CD, Medium, Country_CD, ATT_1, ATT_2, ATT_3 
  FROM myTable UNPIVOT(ind_value FOR Medium IN (aaa_in, bbb_in, ccc_in, ddd_in) ) X
  WHERE ind_value = 1) Y
UNPIVOT(ATT FOR ATT_Col in (ATT_1, ATT_2, ATT_3)) Z;
Enthusiast

Re: Columns to rows

Can we do it in TD_UNPIVOT too since I have TD15.0?

Teradata Employee

Re: Columns to rows

Sure, same idea. You can nest the function calls:

SELECT PROD_CD, Medium, Country_CD, ATT
FROM TD_UNPIVOT( ON (
  SELECT PROD_CD, Medium, Country_CD, ATT_1, ATT_2, ATT_3 
  FROM TD_UNPIVOT( ON (SELECT * FROM myTable)
  USING VALUE_COLUMNS('ind_value') UNPIVOT_COLUMN('Medium') 
  COLUMN_LIST('aaa_in','bbb_in','ccc_in','ddd_in')
  ) X
  WHERE ind_value=1 )  
USING VALUE_COLUMNS('ATT') UNPIVOT_COLUMN('ATT_col')
COLUMN_LIST('ATT_1','ATT_2','ATT_3') ) Z;