Our input table has 12 columns, one for each month, with a unit count in it. I need in our ouput table to turn that into 12 rows, one for each month.

What is the best way to accomplish this?

Example.

Zone,uJan,uFeb,uMar,uApr,uMay,uJun,uJul,uAug,uSep,uOct,uNov,uDec
Z10,5,8,6,7,9,2,5,7,3,9,4,3



I need to get that row outputed to:

Zone,Month,Units
Z10,1,5
Z10,2,8
Z10,3,6
Z10,4,7
Z10,5,9
Z10,6,2
Z10,7,5
Z10,8,7
Z10,9,3
Z10,10,9
Z10,11,4
Z10,12,3

Are you trying to do this via the GUI? If so, have you tried a UNION? I don't know if it'll take it, but you could try typing this directly int the SQL:
SELECT
ZONE,
'1' AS MONTH,
UJAN AS UNITS
FROM
UNION
SELECT
ZONE,
'2' AS MONTH,
UFEB AS UNITS
FROM
UNION
.
.
.

etc.

If it won't allow you to do this (and I suspect it won't), are you open to creating a Data Migrator stored procedure? That way, you can use plain old FOCUS, hold off the results, then use the hold file as the source in your data flow. If you're interested, let me know your email address and I'll send you a write up on how to do that. That's actually what I do most of the time. I do very little via the GUI.

As for how to do this with FOCUS, I have seen several write ups on this sort of thing. I believe the McGuyver technique was created just for this sort of thing. And there is always MATCH, but that can be inefficient depending on your data. Or, if your source is an RDBMS table, you could do a SQL pass-thru and still do a UNION.

You could indeed create a DBMS SQL flow and do a UNION. It's a lot of typing tho, you need a dozen select statements one for each month.

If a FOCUS programmer should read this entry they will surely start blabbering about that 1980's TV show.

But there is a simple DataMigrator solution using another standard SQL construct however, the CROSS JOIN.

Create a synonym with just one column:
FILENAME=mnths, SUFFIX=SQLMSS , $
SEGMENT=MNTHS, SEGTYPE=S0, $
FIELDNAME=MNTH, ALIAS=MNTH, USAGE=I5, ACTUAL=I2, $

Then use the DMC to create the table and to insert twelve (12) rows of data. That gets us the numbers 1 through 12.

Next create a new flow and join the source table to the months table and select a join type of CROSS JOIN which doesn't require any join conditions.

That gives us a so-called cartesian product, every possible combination of the rows from the right and left tables. So with 12 months, if there are two rows in your source, we will get back 12 x 2 or 24 rows, one for each month for each source row.

So now you can simply select ZONE and MNTH. For UNITS we need to specify which month's units to use and that can be done with a CASE statement which you can actually type into the calculator:

CASE
WHEN T2.MNTH = 1 THEN JAN
WHEN T2.MNTH = 2 THEN FEB
WHEN T2.MNTH = 3 THEN MAR
WHEN T2.MNTH = 4 THEN APR
WHEN T2.MNTH = 5 THEN MAY
WHEN T2.MNTH = 6 THEN JUN
WHEN T2.MNTH = 7 THEN JUL
WHEN T2.MNTH = 8 THEN AUG
WHEN T2.MNTH = 9 THEN SEP
WHEN T2.MNTH = 10 THEN OCT
WHEN T2.MNTH = 11 THEN NOV
WHEN T2.MNTH = 12 THEN DEC
END

Here I assumed that the column names were the three letter month abbreviations.

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

You didn't mention what kind of table it is but if you can make it be a fixed file, you can use an alternate master with an OCCURS in it to do exactly what you want.

FILENAME=XXXX, SUFFIX=FIX , $
SEGNAME=ZONE, SEGTYPE=S1, $
FIELDNAME=ZONE, ALIAS=ZONE, USAGE=A3, ACTUAL=A3, $
SEGNAME=MONTHS,SEGTYPE=S0,PARENT=ZONE,OCCURS=12,$
FIELDNAME=UNITS,ALIAS=UNITS,USAGE=I4,ACTUAL=I4,$
FIELDNAME=MOCNTR,ALIAS=ORDER,USAGE=I4,ACTUAL=I4,$

The ALIAS=ORDER field is a virtual counter that will match up with the month.

Simply FILEDEF the file or extract to this master and say (can convert this to SQL):

TABLE FILE XXX
PRINT UNITS
BY ZONE
BY MONCTR AS MONTH
END

This is a very old technique that no one uses anymore (but me, I guess) but can be extremely valuable in the right situation.