Summary table in Teradata SQL

Question

I have the following table in Teradata SQL, called transactions_data:

User_ID      Trans_Date      Amount Purchased
 3134        2012-08-12            3.35
 3135        2012-08-12            4.47
 3134        2012-08-13            4.45

What I am trying to do is create a table in which the columns cover all the dates in table and the rows are how much each user has spent on the day, so, for example:

User_ID       2012-08-12      2012-08-13      2012-08-14
 3134            3.35             4.47            0
 3135            4.47               0             0

Is there an efficient way to do this in Teradata, especially if there are say 365 dates in total (i.e., one year of dates or 2 years of dates?)

Thank you.

Answer 1

If you know in advance the list of dates, you can use conditional aggregation:

select user_id,
    max(case when trans_date = '2012-08-12' then amount_purchased end) as amount_2012_08_12,
    max(case when trans_date = '2012-08-13' then amount_purchased end) as amount_2012_08_13,
    ...
from mytable
group by user_id