Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to transpose column data to row and save into new table?
Go
New
Search
Notify
Tools
Reply
  
How to transpose column data to row and save into new table?
 Login/Join
 
Platinum Member
posted
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, 2005Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to transpose column data to row and save into new table?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.