Archives of the TeradataForum

Message Posted: Wed, 18 Aug 2004 @ 15:34:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Nick

1000 columns !!!!

OK, this is far from elegant and there will certainly be better solutions posted but I would create a second table, generate the sql to populate it, run the SQL to populate it then select from it.

     CREATE TABLE TEST_02
     (NAME  CHAR(04)
     ,DESCRIPTION CHAR(10)
     );

     SEL
     'INSERT INTO TEST_02 SEL NAME,'''||TRIM(BOTH FROM COLUMNNAME)||''' FROM
     TEST_01 WHERE '||TRIM(BOTH FROM COLUMNNAME)||'=1;'

     FROM DBC.COLUMNS

     WHERE
     TABLENAME='TEST_01'
     AND
     COLUMNNAME<>'NAME';

this produces output as follows which I would then run :-

     INSERT INTO TEST_02 SEL NAME,'SHIRT' FROM TEST_01 WHERE SHIRT=1;
     INSERT INTO TEST_02 SEL NAME,'HAT' FROM TEST_01 WHERE HAT=1;
     INSERT INTO TEST_02 SEL NAME,'SOCKS' FROM TEST_01 WHERE SOCKS=1;
     INSERT INTO TEST_02 SEL NAME,'SHOES' FROM TEST_01 WHERE SHOES=1;
     INSERT INTO TEST_02 SEL NAME,'PANTS' FROM TEST_01 WHERE PANTS=1;
     INSERT INTO TEST_02 SEL NAME,'COAT' FROM TEST_01 WHERE COAT=1;

then just run

     SEL * FROM TEST_02 ORDER BY 1,2

the above is probably full of holes as I haven't tested it thoroughly but its the approach I would use.

Howard

	<Prev	Next>		<<First	<Prev	Next>	Last>>