All Forums Database
aycriggb 2 posts Joined 11/15
05 Dec 2015
transpose Rows to Columns

Hi all - I have worked on this for quite some time and read several existing threads, but cannot seem to figure this problem out.
I have "TABLE_A" which contains:

ID        Show        Network        Airs Monday    Airs Tuesday      Airs Wednesday    Airs Thursday    Airs Friday    Airs Weekends
1        AAA            NBC            F                    F                    F                    F                    F                T
2        BBB            NBC            T                    T                    T                    T                    T                F
3        CCC            CBS            F                    F                    T                    F                    F                T
4        DDD            CBS            F                    T                    F                    F                    F                F

which I need to convert to:

ID    Show    Network        Airday
1    AAA        NBC            Weekend
2    BBB        NBC            Monday
2    BBB        NBC            Tuesday
2    BBB        NBC            Wednesday
2    BBB        NBC            Thursday
2    BBB        NBC            Friday
3    CCC        CBS            Wednesday
3    CCC        CBS            Weekend
4    DDD        CBS            Tuesday

I have looked a Dieter's cross-join solutions, which look like valid approaches, but am not clear how to implement.

The threads I have researched are:
http://forums.teradata.com/forum/database/sql-to- transpose-row-to-column-any-level-nth-level-of-data
http://forums.teradata.com/forum/database/transpose- rows-to-columns-0
 
Thank you in advance for any help!

kirthi 65 posts Joined 02/12
07 Dec 2015

Pl check if the code below helps you.
 

CREATE MULTISET TABLE SANDBOX.TABLE_A
(  ID INT
 , SHOW_NM CHAR (3) 
 , NETWRK  CHAR (3) 
 , MON CHAR (1)
 , TUE CHAR (1)
 , WED CHAR (1)
 , THU CHAR (1)
 , FRI CHAR (1)
 , WKENDI CHAR (1)
)  PRIMARY INDEX ( ID);

INSERT INTO  SANDBOX.TABLE_A ( 1, 'AAA' ,  'NBC'  , 'F','F','F','F','F','T') ;
INSERT INTO  SANDBOX.TABLE_A ( 2, 'BBB' ,  'NBC'  , 'T','T','T','T','T','F');
INSERT INTO  SANDBOX.TABLE_A ( 3, 'CCC' ,  'CBS'  , ' F','F','T','F','F','T');
INSERT INTO  SANDBOX.TABLE_A ( 4, 'DDD',  'CBS'  ,  'F','T','F','F','F','F');


CREATE MULTISET TABLE SANDBOX.TEST_SRC2
(  T1 INT
 , TD1 CHAR (3)
)  PRIMARY INDEX ( T1);

INSERT INTO  SANDBOX.TEST_SRC2 ( 1, 'MON' ) ;
INSERT INTO  SANDBOX.TEST_SRC2 ( 2, 'TUE' ) ;
INSERT INTO  SANDBOX.TEST_SRC2 ( 3, 'WED' ) ;
INSERT INTO  SANDBOX.TEST_SRC2 ( 4, 'THU' ) ;
INSERT INTO  SANDBOX.TEST_SRC2 ( 5, 'FRI' ) ;
INSERT INTO  SANDBOX.TEST_SRC2 ( 6, 'WKE' ) ;

SEL 	 S1.ID
   	,S1.SHOW_NM
   	,S1.NETWRK
       	, CASE WHEN S2.T1 = 1 AND S1.MON = 'T' THEN 'MON' 
               WHEN S2.T1 = 2 AND S1.TUE = 'T' THEN 'TUE' 
               WHEN S2.T1 = 3 AND S1.WED = 'T' THEN 'WED' 
               WHEN S2.T1 = 4 AND S1.THU = 'T' THEN 'THU' 
               WHEN S2.T1 = 5 AND S1.FRI = 'T' THEN 'FRI' 
               WHEN S2.T1 = 6 AND S1.WKENDI = 'T' THEN 'WEEKENDS' 
         END C2          
FROM
SANDBOX.TABLE_A S1
CROSS JOIN
SANDBOX.TEST_SRC2 S2
WHERE C2 IS NOT NULL

 

You must sign in to leave a comment.