Archives of the TeradataForum

Message Posted: Mon, 09 Feb 2004 @ 12:53:27 GMT

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

Hi,

A similar problem was provided a solution by the below SQL.

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

You may modify it for your application.

Thanks, Vivek.

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