Archives of the TeradataForum

Message Posted: Fri, 16 Jan 2004 @ 21:35:45 GMT


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

Subj:		Re: How to concatenate the column values

From:		Dieter Noeth

Vivek Pandey wrote:

> C1     C2
> =========
> 1       AB
> 1       BC
> 1       CA

I have to concatenate all the values of C2 in to a single field for a given C1 value like this.

> C1        C2
> ===============
> 1       AB BC CA

Hi Vivek,

this is an example from my trainings:

/*If the maximum number of rows is known (and small)*/

sel
   databasename
  ,tablename
  ,max(case when rnk = 1 then        ColumnName else '' end) ||
   max(case when rnk = 2 then ',' || ColumnName else '' end) ||
   max(case when rnk = 3 then ',' || ColumnName else '' end) ||
   max(case when rnk = 4 then ',' || ColumnName else '' end) ||
   max(case when rnk = 5 then ',' || ColumnName else '' end) ||
   max(case when rnk = 6 then ',' || ColumnName else '' end) ||
   max(case when rnk = 7 then ',' || ColumnName else '' end) ||
   max(case when rnk = 8 then ',' || ColumnName else '' end) ||
/*** Indicating more than 8 rows ***/
   max(case when rnk > 8 then ',...' else '' end) as Columns
from
  (
   sel
     databasename
    ,tablename
    ,trim(columnName) as ColumnName
    ,rank() over (partition by databasename, tablename
                  order by columnid) as rnk
   from
     dbc.columns
   where databasename = 'dbc'
  ) dt
group by 1,2
order by 1,2
;

/*If the maximum number of rows is unknown (or huge)
   But it returns more than one line... */

sel
   databasename
  ,tablename
  ,trim(((rnk / 8) * 8) + 1 (format '999')) || ' to ' ||
   trim(((rnk / 8) + 1) * 8 (format '999')) as ColumnNumber
  ,max(case when rnk mod 8 = 0 then ColumnName else '' end) ||
   max(case when rnk mod 8 = 1 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 2 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 3 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 4 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 5 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 6 then ',' || ColumnName else '' end) ||
   max(case when rnk mod 8 = 7 then ',' || ColumnName else '' end) as
Columns
from
  (
   sel
     databasename
    ,tablename
    ,trim(columnName) as ColumnName
    ,rank() over (partition by databasename, tablename
                  order by columnid) -1 as rnk
   from
     dbc.columns
   where databasename = 'dbc'
  ) dt
group by 1,2,3
order by 1,2,3
;

Dieter


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

Attachments

Library

Quick Reference