October 26, 2005, 12:02 PM
Motiejustransforming columns to rows :) :)
Maybe anybody has some interesting ideas how to put few columns into one?
there is table:
--------------------------------
| ID | Name1 | Name2 | Name3 |
--------------------------------
| 1 | Ann | Merry | Lucy |
--------------------------------
| 2 | Natalie | Megan | Jill |
--------------------------------
...
What do you think? Is there a posibility to
transform this table and print those beautiful names in one column?
for instance transform and hold table to TRANSFORMED_DATA and then report:
TABLE FILE TRANSFORMED_DATA
PRINT MY_TRANSFORMED_DATA
WHERE ID EQ 1
END
the output should be :
-----------------------
| MY_TRANSFORMED_DATA |
-----------------------
| Ann |
-----------------------
| Merry |
-----------------------
| Lucy |
-----------------------
I have one wery cool way:
-*-----------------------------
TABLE FILE NAMES
PRINT NAME1 ID
ON TABLE HOLD AS N1
END
DEFINE FILE N1
MY_DATA/A40 = NAME1;
END
-*-----------------------------
TABLE FILE NAMES
PRINT NAME2 ID
ON TABLE HOLD AS N2
END
DEFINE FILE L2
MY_DATA/A40 = NAME2;
END
-*-----------------------------
TABLE FILE NAMES
PRINT NAME3 ID
ON TABLE HOLD AS N3
END
DEFINE FILE L3
MY_DATA/A40 = NAME3;
END
-*-----------------------------
MATCH
FILE N1
BY MY_DATA BY ID
RUN
FILE N2
BY MY_DATA BY ID
RUN
FILE N3
BY MY_DATA BY ID
AFTER MATCH HOLD AS MY_T_DATA OLD-OR-NEW
END
-*-----------------------------
TABLE FILE MY_T_DATA
PRINT MY_DATA
WHERE ID EQ 1
END
-*-----------------------------
But isn't it confusing???
Any suggestions are welcome!!!
October 26, 2005, 01:00 PM
PrarieSomething like this.
TABLE FILE NAMES
PRINT
ID AS ' ' OVER
NAME1 AS ' ' OVER
NAME2 AS ' ' OVER
NAME3
END
October 26, 2005, 02:24 PM
Francis MarianiIf you need to save the data in a hold file, try the following:
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
TABLE FILE CAR
SUM
COUNTRY/A40 AS 'NAME'
BY COUNTRY NOPRINT
ON TABLE HOLD AS HOLD01
RUN
SUM
MODEL/A40 AS 'NAME'
BY MODEL NOPRINT
ON TABLE HOLD AS HOLD02
RUN
SUM
CAR/A40 AS 'NAME'
BY CAR NOPRINT
ON TABLE HOLD AS HOLD03
END
TABLE FILE HOLD01
SUM NAME
BY NAME NOPRINT
MORE
FILE HOLD02
MORE
FILE HOLD03
END