How to transpose column data to row and save into new table?

Hello all,

I have a request to transpose column data from source table to rows in target table. Here is the detail:

TB_SOURCE has 1 ID field and 3 data field in same format, such as:

SRC_ID (A10)
FLD1 (A8)
FLD2 (A8)
FLD3 (A8)

TB_TARGET has 1 ID field and 1 Data field, such as:

TAR_ID (A10)
TAR_DATA (A8)

I need to copy each row of 3 data fields from source table into 3 rows of target table, and copy SRC_ID into TAR_ID for 3 times, just like:

TB_TARGET.TAR_ID = TB_SOURCE.SRC_ID
TB_TARGET.TAR_DATA = TB_SOURCE.FLD1

(NEXT ROW of Target)

TB_TARGET.TAR_ID = TB_SOURCE.SRC_ID
TB_TARGET.TAR_DATA = TB_SOURCE.FLD2

(NEXT ROW of Target)

TB_TARGET.TAR_ID = TB_SOURCE.SRC_ID
TB_TARGET.TAR_DATA = TB_SOURCE.FLD3

(Go to next row of Source and repeat until end of table)
.....

Please demostrate both adhoc mode and ETL mode to accomplish this task if possible. Thank you.

Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005

mgrackin

Virtuoso

posted

Hide Post

I have never used the ETL tools but here is an example showing how to do this using a MASTER FILE DESCRIPTION with the OCCURS clause. This is a technique I have used on occasion. I use a -WRITE to create the MFD in this example but you can just create it with any editor and then make sure WebFOCUS can find it in the APP PATH. At the end you can add an additional ON TABLE SAVE statement to create a final hold file with the rows in it.

FILEDEF MAKEROWS DISK makerows.mas
-RUN
-WRITE MAKEROWS FILENAME=MAKEROWS, SUFFIX=FIX,$
-WRITE MAKEROWS SEGNAME=ONE, SEGTYPE=S0,$
-WRITE MAKEROWS FIELDNAME=COUNTRY , , FORMAT=A10, ACTUAL=A10,$
-WRITE MAKEROWS SEGNAME=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=3,$
-WRITE MAKEROWS FIELDNAME=XCOST , , FORMAT=D7 , ACTUAL=A7 ,$
FILEDEF MAKEROWS CLEAR
-RUN
DEFINE FILE CAR
RCOST2/D7=RCOST*2;
RCOST3/D7=RCOST*3;
END
TABLE FILE CAR
SUM RCOST RCOST2 RCOST3
BY COUNTRY
ON TABLE SAVE AS MAKEROWS
END
-RUN
TABLE FILE MAKEROWS
PRINT XCOST
BY COUNTRY
END

Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003

Please Wait. Your request is being processed...