Archives of the TeradataForum

Message Posted: Tue, 30 Mar 2004 @ 15:26:33 GMT

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

This question seems to arise again & again in the forum. Here is the most elegant answer which Dieter provided once for such a scenario.

Vivek Pandey wrote:

> 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
;

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