Hi

Let's say I have following table:

Table Car:

Country || CAR

England || Jaguar
Japan || Toyota
Italy || Maserati
France || Peugeot


into:

England || Japan || Italy || France
Jaguar || Toyota || Maserati || Peugeot

I try Pivot topic, but no luck(maybe I'm not using Pivot correctly), any other way to transpose this table?

This message has been edited. Last edited by: nox,

Nox

Try this...

 
TABLE FILE CAR
SUM 
     CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,
$
ENDSTYLE
END
 

Hi Chuck,

Nice code! But I have realize that I did not mansion about second part of my objective.

My next objective is able to transpose the "CAR" table result into a DB not on report. and another condition is that each column's rows may be increment through time(meaning there will be more country add into "Country" column and more car into "CAR" column as well)

How to accomplish this within iWay DMC?

Nox

I am confused by this requirement. As you saw by my example that the data can be displayed how you want in a report after it is loaded in the table by using the ACROSS. I don't understand why the data needs to be loaded into the db the way you describe. The rows of data can be country and car and then you can insert additional rows easily.

Hi Chuck,

I know it is sound confused in logical view, but what actually happen is due to some older DB system that contain some tables wish to transpose and load into new DB.
Since there is some bad designing in older DB system.

Assume in old DB's table look like this:

Infro. || Data1 || Data2
------------------------------
ID || ABC1234 || ABC456
FName || Mike || Anna
LName || Kennedy || Snow
Sex || Male || Female

Nox

This post may help you but I strongly urge you to no perpetuate a bad design.

http://iwayetl/blogs/blog1.php...more=1&c=1&tb=1&pb=1

the link is not working.

Here are the contents of the link..

You can store anything in a three column database
07/03/08 | by Clif [mail] | Categories: DataMigrator, SQL
I was cleaning up my desk before the holiday and found something I'd written a while back in response to a prospect's request. I had thought about proposing an enhancement to DataMigrator to automate this process, but it died due to lack of interest. However, I think others may find it interesting so I'm posting it here.

How to pivot a table containing name and value pairs
While most input data is represented in a columnar structure, it's also possible to represent data with name and value pairs. This is common for XML structures but is also used for flat files.

It could also be used with a relational database. The same three column table could be used to store everything. But that would be madness, right? (Actually it would be called NoSQL) When input data is represented this way, it's necessary to "pivot" the data to load it into a normal relational table.

For example consider the following table:

1Last Name ADAMKIEWICZ
1First Name GREGORY
1Position Line Worker
2Last Name ADAMS
2First Name GAY
2Position Line Worker
3Last Name ALBOR
3First Name STEVEN
3Position Technician
4Last Name ALSMAN
4First Name RANDY
4Position Line Worker
5Last Name ARNOLD
5First Name HAROLD
5Position Technician
6Last Name AUSTIN
6First Name BARRY
6Position Technician
7Last Name BALMER
7First Name PETER
7Position Technician
8Last Name BALNAVE
8First Name WILLIAM
8Position Line Manager
The first column of each table is an ID number, the second is a Name such as "Last Name" or "First Name" and the third column is the value. This file could be described by the following synonym:

FILENAME=hrflat, SUFFIX=FIX ,
DATASET=baseapp/hrflat.ftm, $
SEGMENT=HRFLAT, SEGTYPE=S0, $
FIELDNAME=ID_NUM, ALIAS=ID_NUM, USAGE=I2, ACTUAL=A2, $
FIELDNAME=PNAME, ALIAS=PNAME, USAGE=A13, ACTUAL=A13, $
FIELDNAME=PVALUE, ALIAS=PVALUE, USAGE=A12, ACTUAL=A12, $

In order to load the data into a relational database, the table must be pivoted, or flipped, with the result:

ID_NUM Last Name First Name Position
1 ADAMKIEWICZ GREGORY Line Worker
2 ADAMS GAY Line Worker
3 ALBOR STEVEN Technician
4 ALSMAN RANDY Line Worker
5 ARNOLD HAROLD Technician
6 AUSTIN BARRY Technician
7 BALMER PETER Technician
8 BALNAVE WILLIAM Line Manager
As it happens, this type of pivot is trivial to in FOCUS using TABLE and the verb ACROSS:

TABLE FILE HRFLAT
WRITE PVALUE BY ID_NUM ACROSS PNAME
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET ASNAMES ON
END

The disadvantage of this approach is that it requires writing a TABLE request, there's no way to do this in a DM flow, so using this data would be two-step process, first run the TABLE to create a flat file, then load it into a relational table. With the setting for ASNAME the generated synonymn has column names of PVAFirst Name, PVALast Name, PVAPosition. That may mean editing the generated synonym to enter the desired column names.

It's possible to write a SQL SELECT statement to do the same thing.

SELECT
ID_NUM ,
MAX( CASE WHEN PNAME = 'Last Name' THEN PVALUE END
AS "Last Name" ,
MAX( CASE WHEN PNAME = 'First Name' THEN PVALUE END )
AS "First Name" ,
MAX( CASE WHEN PNAME = 'Position' THEN PVALUE END )
AS "Position"
FROM HRFLAT T1
GROUP BY ID_NUM;

This could even be done using the DataMigrator user interface in the Column Selection grid:



The disadvantage of this approach is that you need to know all the possible values for the name column to write the select statement. However once you do that, the synonym that is generated has the correct column names.

quote:

SELECT
ID_NUM ,
MAX( CASE WHEN PNAME = 'Last Name' THEN PVALUE END
AS "Last Name" ,
MAX( CASE WHEN PNAME = 'First Name' THEN PVALUE END )
AS "First Name" ,
MAX( CASE WHEN PNAME = 'Position' THEN PVALUE END )
AS "Position"
FROM HRFLAT T1
GROUP BY ID_NUM;

I try using this method, but I end up something like this:

ID_NUM || Last Name || First Name || Position
1 || ADAMKIEWICZ || NULL || NULL
2 || NULL || GREGORY || NULL
3 || NULL || NULL || Line Worker
4 || ADAMS || NULL || NULL
5 || NULL || GAY || NULL
6 || NULL || NULL || Line Worker

since GROUP BY ID_NUM then how can I get the result like:

quote:

ID_NUM Last Name First Name Position
1 ADAMKIEWICZ GREGORY Line Worker
2 ADAMS GAY Line Worker
3 ALBOR STEVEN Technician
4 ALSMAN RANDY Line Worker
5 ARNOLD HAROLD Technician
6 AUSTIN BARRY Technician
7 BALMER PETER Technician
8 BALNAVE WILLIAM Line Manager

Nox

My bad, I didn't test this out... The first problem is that Focal Point didn't keep your input data aligned and the SQL had a missing right ) I am going to e-mail you updated input file and SQL