Is there a way to do use FOCUS code instead of SQL to convert columns to rows, as in this SQL example:

SQL
SELECT 'DEALER_COST' AS COLNAME, DEALER_COST AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'RETAIL_COST' AS COLNAME, RETAIL_COST AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'SEATS' AS COLNAME, SEATS AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'SALES' AS COLNAME, SALES AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND';
TABLE ON TABLE HOLD AS H001 FORMAT ALPHA
END

Thanks,

Match file can do it.


It's essential to pre-summarize the data, so there is one row per set of sort-key values. (That will allow us to treat the Value as a sort-key.)

Use Define (against the summed data) to create name and value vars with consistent formats, for all the dependent fields of interest.

Then issue a multi-leg Match file, one OLD-OR-NEW merge per UNION.

MATCH 
FILE HOLD
COUNT ENTRIES NOPRINT 
 BY {the "real" keys, if applicable}
 BY Name1  AS COLNAME
 BY Value1 AS COLVALUE
RUN
...

Oh, well - it sounds like it's a lot easier with SQL.

Thanks,

De gustibus ... this looks no more complicated to me:

DEFINE FILE CAR
  NAME1/A12 ='DEALER_COST ';  VALUE1/D8.1= DEALER_COST  ;
  NAME2/A12 ='RETAIL_COST ';  VALUE2/D8.1= RETAIL_COST  ;
  NAME3/A12 ='SALES       ';  VALUE3/D8.1= SALES        ;
  NAME4/A12 ='LENGTH      ';  VALUE4/D8.1= LENGTH       ;
  NAME5/A12 ='WIDTH       ';  VALUE5/D8.1= WIDTH        ;
  NAME6/A12 ='HEIGHT      ';  VALUE6/D8.1= HEIGHT       ;
  NAME7/A12 ='WEIGHT      ';  VALUE7/D8.1= WEIGHT       ;
  NAME8/A12 ='WHEELBASE   ';  VALUE8/D8.1= WHEELBASE    ;
END
MATCH FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME1 AS COLNAME BY VALUE1 AS COLVALUE
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME2 AS COLNAME BY VALUE2 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME3 AS COLNAME BY VALUE3 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME4 AS COLNAME BY VALUE4 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME5 AS COLNAME BY VALUE5 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME6 AS COLNAME BY VALUE6 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME7 AS COLNAME BY VALUE7 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
  BY COUNTRY BY CAR BY MODEL BY NAME8 AS COLNAME BY VALUE8 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD PRINT COLVALUE
BY COUNTRY BY CAR BY MODEL BY COLNAME
END

Hi Francis.

Hope all is well back in Canada.

How are you going to use the HOLD file? I am assuming that since you are putting the column titles in the rows you don't need a master file. My rows don't come out in the same order as yours but that should be fixable.

I used the following code:

 
TABLE FILE CAR
PRINT      DEALER_COST
    OVER RETAIL_COST
 OVER SEATS
 OVER SALES
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS H001 FORMAT LOTUS
-*ON TABLE HOLD AS H001 FORMAT WP
ON TABLE SET PAGE NOLEAD
END

WP output:

  DEALER_COST      7,427
  RETAIL_COST      8,878
  SEATS                2
  SALES                0
  DEALER_COST     11,194
  RETAIL_COST     13,491
  SEATS                5
  SALES            12000
  DEALER_COST     14,940
  RETAIL_COST     17,850
  SEATS                4
  SALES                0
  DEALER_COST      4,292
  RETAIL_COST      5,100
  SEATS                2
  SALES                0
 

LOTUS output:

"DEALER_COST",   7427
"RETAIL_COST",   8878
"SEATS",  2
"SALES",     0
"DEALER_COST",  11194
"RETAIL_COST",  13491
"SEATS",  5
"SALES", 12000
"DEALER_COST",  14940
"RETAIL_COST",  17850
"SEATS",  4
"SALES",     0
"DEALER_COST",   4292
"RETAIL_COST",   5100
"SEATS",  2
"SALES",     0
  

...and... if you want to use constants instead of the field names you could DEFINE or COMPUTE those values and use AS '' for all the fields.

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

JG - nice technique for using MATCH to UNION rather than JOIN - I'm sure that I will find a use.

You could go the McGuiver route

JOIN ONE WITH LENGTH IN CAR TO ALL ONE IN MCGUYVER AS J1

DEFINE FILE CAR
NAME/A12 = IF MCVAL EQ 1 THEN 'DEALER_COST '
ELSE IF MCVAL EQ 2 THEN 'RETAIL_COST '
...
VALUE/D8.1= IF MCVAL EQ 1 THEN DEALER_COST
ELSE IF MCVAL EQ 2 THEN RETAIL_COST
...
END
TABLE FILE CAR
PRINT
NAME
VALUE
BY COUNTRY BY CAR BY MODEL BY MCVAL NOPRINT
END

This should be built into wf/SQL just like mcguyver with my MULTIPLY FILE ... AT ... TIMES syntax

Its a shame that OVER does not hold values in the same way as the report.

Could RECTYPEs be adapted in some way?

select over (x1, x2 ... xn) AS namecol valuecol

could be the syntax - will drop ANSI commitee an email.

Here is another way to do it.

TABLE FILE CAR
PRINT COMPUTE NAME1/A20='DEALER_COST' ;
      DEALER_COST/I9
      COMPUTE NAME2/A20='RETAIL_COST' ;
      RETAIL_COST/I9
      COMPUTE NAME3/A20='SEATS' ;
      SEATS/I9
      COMPUTE NAME4/A20='SALES' ;
      SALES/I9
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SAVE AS TMP_FILE FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END

-RUN

EX -LINES 5 EDAPUT MASTER,TMP_FILE ,CV,FILE
FILENAME=TMP_FILE , SUFFIX=FIX,$
SEGNAME=TMP_FILE ,OCCURS=4, $
  FIELD=COLNAME ,ALIAS=  ,A20 ,A20 ,$
  FIELD=COLVALUE,ALIAS=  ,I9  ,A9  ,$

-RUN

TABLE FILE TMP_FILE
PRINT *
END