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:
Thank you in advance for any help!
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' ) ;
, 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'
WHERE C2 IS NOT NULL