Move duplicated records from row view to across column view

I am very new to the FOCUS world and would like some direction. I am using an older version of Mainframe FOCUS (i.e., not WebFOCUS) and I am not sure if I can do the following within the FOCUS environment.

I table consists of 3 fields (Invoice, Part Number and Part Description) and because multiple part numbers can be on the same invoice, the table has duplicated invoices. I would like my results table to display only 1 invoice per row (below is an example).

What is the best approach to accomplish this? I have done this via SQL (script below).

Thank you in advance for the assistance.

Table:

INVOICE PART_NBR PART_DESC
12345 215487 AAABBCC
12345 245178 BBAACC
12346 234578 BBCACA
12456 324512 ABABAB
12457 234578 BBCACA
12457 245178 BBAACC

Wanted Results:

INVOICE PART_NBR_1 PART_DESC_1 PART_NBR_2 PART_DESC_2
12345 215487 AAABBCC 245178 BBAACC
12346 234578 BBCACA
12456 324512 ABABAB
12457 234578 BBCACA 245178 BBAACC


SQL Script:

SELECT T.FCLTY_NO, T.VIN, T.WO_NO, T.WOL_NO, T.WO_OPEN_DT, T.WO_COMPL_DT, T.LAST_SUBM_DT, T.VEH_MILE_QT, T.APPR_CVR_CD,
T.PNC_CD, T.PNC_TX, T.PFP, T.TECH_ID, T.UKEY,
MAX(CASE WHEN T.SEQ_NO = 1 THEN T.SEQOPCD END) OPCD,
MAX(CASE WHEN T.SEQ_NO = 2 THEN T.SEQOPCD END) OPCD1,
MAX(CASE WHEN T.SEQ_NO = 3 THEN T.SEQOPCD END) OPCD2,
MAX(CASE WHEN T.SEQ_NO = 4 THEN T.SEQOPCD END) OPCD3,
MAX(CASE WHEN T.SEQ_NO = 5 THEN T.SEQOPCD END) OPCD4
FROM
(SELECT DISTINCT A.FCLTY_NO, B.SEQ_NO, B.OPER_CD, B.SEQ_NO||B.OPER_CD SEQOPCD, E.DESC_TX OPCD_DESC, A.VIN_PFX_CD||A.VIN_SER_NO VIN, A.WO_NO, A.WOL_NO, C.WO_OPEN_DT, C.WO_COMPL_DT,
C.LAST_SUBM_DT, C.VEH_MILE_QT, A.APPR_CVR_CD, A.PNC_CD, F.PNC_TX, D.PART_NO PFP, H.TECH_ID, A.VIN_PFX_CD||A.VIN_SER_NO||A.WO_NO||A.WOL_NO UKEY
FROM DRBA.SVC_WOL_COMN_T A,
DRBA.SVC_WOL_OPER_T B,
DRBA.SVC_WO_T C,
DRBA.SVC_PRIM_PART_T D,
DRBA.SVC_OPER_T E,
DRBA.VSM_PNC_T F,
USER9.TBL_VDECIDE_VINS G,
DRBA.SVC_TECH_ID_T H
WHERE G.VIN_PFX_CD = A.VIN_PFX_CD
AND G.VIN_SER_NO = A.VIN_SER_NO
AND A.FCLTY_NO = B.FCLTY_NO (+)
AND A.VIN_PFX_CD = B.VIN_PFX_CD (+)
AND A.VIN_SER_NO = B.VIN_SER_NO (+)
AND A.WO_NO = B.WO_NO (+)
AND A.WOL_NO = B.WOL_NO (+)
AND A.FCLTY_NO = C.FCLTY_NO (+)
AND A.VIN_PFX_CD = C.VIN_PFX_CD (+)
AND A.VIN_SER_NO = C.VIN_SER_NO (+)
AND A.WO_NO = C.WO_NO (+)
AND A.FCLTY_NO = D.FCLTY_NO (+)
AND A.VIN_PFX_CD = D.VIN_PFX_CD (+)
AND A.VIN_SER_NO = D.VIN_SER_NO (+)
AND A.WO_NO = D.WO_NO (+)
AND A.WOL_NO = D.WOL_NO (+)
AND A.FCLTY_NO = H.FCLTY_NO (+)
AND A.VIN_PFX_CD = H.VIN_PFX_CD (+)
AND A.VIN_SER_NO = H.VIN_SER_NO (+)
AND A.WO_NO = H.WO_NO (+)
AND A.WOL_NO = H.WOL_NO (+)
AND B.OPER_CD = E.OPER_CD
AND A.PNC_CD = F.PNC_CD
AND C.LAST_SUBM_DT BETWEEN '01APR2015' AND '31AUG2015'
ORDER BY A.FCLTY_NO, A.VIN_PFX_CD||A.VIN_SER_NO, A.WO_NO, A.WOL_NO, B.SEQ_NO) T
GROUP BY T.FCLTY_NO, T.VIN, T.WO_NO, T.WOL_NO, T.WO_OPEN_DT, T.WO_COMPL_DT, T.LAST_SUBM_DT, T.VEH_MILE_QT, T.APPR_CVR_CD,
T.PNC_CD, T.PNC_TX, T.PFP, T.TECH_ID, T.UKEY
ORDER BY T.FCLTY_NO, T.VIN, T.UKEY

This message has been edited. Last edited by: FP Mod Chuck,

Using FOCUS you need to perform something such as (assuming that a master file exist for SVC_WOL_COMN_T)

TABLE FILE SVC_WOL_COMN_T
SUM PART_NBR PART_DESC
ACROSS INVOICE
WHERE ...
END

Or you can use the SQL pass-through technic with FOCUS.
But you'll need to replace xyz and connection_abc with the proper values.

ENGINE xyz SET DEFAULT_CONNECTION conection_abc
SQL xyz PREPARE SQLOUT FOR
...Put your SQL query here...
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
END
-RUN

TABLE FILE RPT_DATA
SUM PART_NBR PART_DESC
ACROSS INVOICE
WHERE ...
END

Thank you, I'll give it a try. Regarding the SQL pass-through, I am not familiar with it, nor are any of my team members; where can I get information on it?

Also, why the Invoice across and not the part number?

FOCUS for Mainframe > Creating Reports > Using SQL to Create Reports.

The ACROSS column is the Dimension, not the Measure:

Print Measure [By/Across] Dimension.

Hi Casaler,
First, welcome to the WebFOCUS forum.
Then, could you update your signature - location, WF version etc.?
As for your question, here is a possible solution:

  
-* File casaler01.fex
-* Create an in-memory master for TBL
EX -LINES 6 EDAPUT MASTER,TBL,C,MEM
FILENAME=TBL, SUFFIX=FIX
SEGNAME=TBL, SEGTYPE=S0
FIELDNAME=INVOICE, ALIAS=INV, FORMAT=I5, ACTUAL=A5,$
FIELDNAME=PART_NBR, ALIAS=PNUM, FORMAT=I6, ACTUAL=A6,$
FIELDNAME=PART_DESC, ALIAS=PDESC, FORMAT=A10, ACTUAL=A10,$
-RUN
-* Logical data file TBL
FILEDEF TBL DISK TBL.FTM
-RUN
-* Put data into physical file TBL
-WRITE TBL 12345215487AAABBCC
-WRITE TBL 12345245178BBAACC
-WRITE TBL 12346234578BBCACA
-WRITE TBL 12456324512ABABAB
-WRITE TBL 12457234578BBCACA
-WRITE TBL 12457245178BBAACC
-RUN
-* Number the parts per invoice
DEFINE FILE TBL
PP/I2=IF INVOICE NE LAST INVOICE THEN 1 ELSE PP + 1;
END
-* Output
TABLE FILE TBL
SUM PNUM PDESC
BY INVOICE ACROSS PP
END