Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 27 Jan 2011 @ 12:50:25 GMT


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


Subj:   Need help with Dynamic Pivoting
 
From:   samidaha

Hi all,

I know the pivot subject was discussed before but I have not found a proper solution to my needs.

I have the following table :

     Date         productID   ProductName   Products_Sold
     2011-01-07   111         AAA                      10
     2011-01-07   222         BBB                      20
     2011-01-07   333         CCC                      30
     2011-01-14   111         AAA                      15
     2011-01-14   222         BBB                      25
     2011-01-21   222         BBB                      35
     2011-01-21   333         CCC                      15

I want the query will return:

     productID   ProductName   2011-01-07   2011-01-14   2011-01-21
     111         AAA                   10           15            0
     222         BBB                   20           25           35
     333         CCC                   30            0           15

I am using the following query to achieve that and it works perfectly -

     SELECT      productID ,          ProductName
     max(case when date=( '2011-01-07') then
     Products_Sold else 0  end )    as date_2011_01_07,
     max(case when date =( '2011-01-14') then
     Products_Sold else 0  end )    as date_2011_01_14,
     max(case when date =( '2011-01-21') then
     Products_Sold else 0  end ) as date_2011_01_21 from TABLE group by 1,2 order by productID

My special needs are: every week a set of products entering the table,

1. Is there a way to do this pivoting without changing the query every week?

2.Is there a way to define the columns names to be dates and not string?


Thanks,

Sami



     
  <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