transpose Rows to Columns

Database

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!

1 REPLY
Enthusiast

Re: transpose Rows to Columns

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