# Column into multiple rows

Author Message
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Thu Mar 03, 2016 8:22 pm

Hi,

My input file is of FB and LRECL = 7 bytes all numeric. The input data is like below.

 Code: 1234567 8901234 5678526 5267486 4632576 3215485 8521437 5241879 5553337 5421557 6521412 2451122 5511224 7845124 5411228 9955864 5211586

The expected output is below. The column needs to be split into rows in such a way that each row contains 7 numbers from the column as shown below.

 Code: '1234567','8901234','5678526','5267486','4632576','3215485','8521437' ,'5241879','5553337','5421557','6521412','2451122','5511224','7845124' ,'5411228','9955864','5211586'

Is it possible to do this using syncsort? I first googled and tried the below card. But it failed since RESIZE operator doesn't exist in SYNCSORT.

 Code: //TOOLIN   DD  *                                                RESIZE FROM(IN) TO(OUT) TOLEN(80) USING(CTL1)                  /*                                                              //CTL1CNTL DD  *                                                  INREC BUILD=(C',''',1,7,C'''',60X,71:SEQNUM,10,ZD)              OUTFIL FNAMES=OUT,IFTHEN=(WHEN=(71,10,ZD,EQ,1),OVERLAY=(1:X)) /*

So, did the below solution in syncsort in 2 steps.

 Code: Step1: SORT FIELDS=COPY                            INREC IFTHEN=(WHEN=GROUP,RECORDS=7,                PUSH=(11:ID=7,20:SEQ=1))              OUTREC IFTHEN=(WHEN=(20,1,ZD,EQ,1),                BUILD=(11,8,C'\$#',1,7,C'#',70Z)),            IFTHEN=(WHEN=(20,1,ZD,EQ,2),                BUILD=(11,8,10Z,C'\$#',1,7,C'#',60Z)),       IFTHEN=(WHEN=(20,1,ZD,EQ,3),                BUILD=(11,8,20Z,C'\$#',1,7,C'#',50Z)),       IFTHEN=(WHEN=(20,1,ZD,EQ,4),                BUILD=(11,8,30Z,C'\$#',1,7,C'#',40Z)),       IFTHEN=(WHEN=(20,1,ZD,EQ,5),                BUILD=(11,8,40Z,C'\$#',1,7,C'#',30Z)),       IFTHEN=(WHEN=(20,1,ZD,EQ,6),                BUILD=(11,8,50Z,C'\$#',1,7,C'#',20Z)),       IFTHEN=(WHEN=(20,1,ZD,EQ,7),                BUILD=(11,8,60Z,C'\$#',1,7,C'#',10Z))   OUTFIL NODETAIL,REMOVECC,                        SECTIONS=(1,7,TRAILER3=(1,8,             TOT=(9,4,BI,BI,LENGTH=4),    TOT=(13,4,BI,BI,LENGTH=4),    TOT=(17,4,BI,BI,LENGTH=4),    TOT=(21,4,BI,BI,LENGTH=4),    TOT=(25,4,BI,BI,LENGTH=4),    TOT=(29,4,BI,BI,LENGTH=4),    TOT=(33,4,BI,BI,LENGTH=4),    TOT=(37,4,BI,BI,LENGTH=4),    TOT=(41,4,BI,BI,LENGTH=4),    TOT=(45,4,BI,BI,LENGTH=4),    TOT=(49,4,BI,BI,LENGTH=4),    TOT=(53,4,BI,BI,LENGTH=4),    TOT=(57,4,BI,BI,LENGTH=4),    TOT=(61,4,BI,BI,LENGTH=4),    TOT=(65,4,BI,BI,LENGTH=4),    TOT=(69,4,BI,BI,LENGTH=4),    TOT=(73,4,BI,BI,LENGTH=4),    TOT=(77,4,BI,BI,LENGTH=4),    TOT=(81,4,BI,BI,LENGTH=4),     TOT=(85,4,BI,BI,LENGTH=4))) END                            Step2:   SORT FIELDS=COPY                                      INREC IFTHEN=(WHEN=(1,7,ZD,EQ,1),BUILD=(10,79,1X)),               IFTHEN=(WHEN=NONE,BUILD=(9,80))          OUTREC FIELDS=(1,80,TRAN=ALTSEQ)                      ALTSEQ CODE=(5B6B,7B7D,0040)                         END

This one works, but the code is very big. So, is there any way to optimize this?

Regards
Amar
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Fri Mar 04, 2016 5:52 am Add an ID to your WHEN=GROUP. Using the PUSHed SEQ, five more WHEN=GROUPs, with BEGIN= for the sequence being 2, 3, 4, 5, 6, and RECORDS descending (6, 5, 4, 3, 2, 1). Each will PUSH the current data to a different location. Further use OUTFIL reporting features with SECTIONS and TRAILER3. "break" on the ID. Use NODETAIL. TRAILER3 includes each number from the different locations prefixed/suffixed by a constant for the delimiter.
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

 Posted: Fri Mar 04, 2016 11:35 pm Thank you Bill for the response. I will try and let you know. Regards Amar
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Column names in SYSIBM tables DB2 5 Can we Insert duplicates in Primary U... DB2 2 NOT ABLE TO FIND DB2 ROWS DB2 2 Need to read duplicate rows from tabl... DB2 3 Creating a single-record file with CO... DFSORT/ICETOOL 8
Search our Forums: