Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Normalizing in TSQL...
 
From:   Vivek Pandey

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


     
  <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: 15 Jun 2023