Home Page for the TeradataForum
 

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.

OR

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

OR

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016