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
4Last Name ALSMAN
4First Name RANDY
4Position Line Worker
5Last Name ARNOLD
5First Name HAROLD
6Last Name AUSTIN
6First Name BARRY
7Last Name BALMER
7First Name PETER
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 ,
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
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.
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 )
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats