Archives of the TeradataForum

Message Posted: Wed, 29 Jan 2003 @ 20:20:28 GMT


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

Subj:		Re: Result on one line rather than in column.

From:		Dieter N�th

Philippe VILLENEUVE wrote:

I have this SQL command

select a.ColumnName
FROM DBC.Columns a
WHERE a.DatabaseName='xxxx' and a.TableName='zzzzz'

Result :

DATE_DEBU_VALD_INFO
CODE_ETAT_GENR
NUMR_ABNN_VIDT
DATE_ADHS
CODE_ABNN_ACTF
NATR_RESI_ABNN_VIDT
CODE_TARF
TYPE_CONT_VIDT
NUMR_PERS
CODE_PRDT_SERV
CODE_ORGN_FINN

how can i have the result on one line : DATE_DEBU_VALD_INFO, CODE_ETAT_GENR, NUMR_ABNN_VIDT,....?

Do it in the client using Perl/PHP/Your favourite programming language.

Use a cursor within a Stored Procedure. This is probably the best way if you want it only for a single table.

Write some ugly looking but efficient SQL:

select
   max(case when rnk = 1 then trim(a.ColumnName) else '' end) ||
   max(case when rnk = 2 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 3 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 4 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 5 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 6 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 7 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 8 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 9 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 10 then ',' || trim(a.ColumnName) else '' end) ||
/*** probably more copy and paste ***/
   max(case when rnk > 10 then ',...' else '' end)   as Columns
from
   (
     select
       ColumnName
       ,rank() over (order by columnid) as rnk
     from DBC.Columns
     where
       DatabaseName='dbc'
     and
       TableName='tables'
   ) a

You can do it, because the maximum number of columns is known in advance (256 in R4, but too many in R5, > 2000)

And you can easily get a list of columns for more than one table:

select
   Tablename,
   max(case when rnk = 1 then trim(a.ColumnName) else '' end) ||
   max(case when rnk = 2 then ',' || trim(a.ColumnName) else '' end) ||
   max(case when rnk = 3 then ',' || trim(a.ColumnName) else '' end) ||
...
   max(case when rnk = 255 then ',' || trim(a.ColumnName) else '' end)
||
   max(case when rnk = 256 then ',' || trim(a.ColumnName) else '' end)
as Columns
from
   (
     select
       Tablename,
       ColumnName
       ,rank() over (partition by Tablename order by columnid) as rnk
     from DBC.Columns
     where
       DatabaseName='dbc'
   ) a
group by Tablename

Dieter


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

Attachments

Library

Quick Reference