Archives of the TeradataForum

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

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

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