Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Stored Procedure Performance and Transposing Data
 
From:   Vivek Pandey

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