CODEWITHSUNDEEP
sqlcountpivotteradataaggregate-functions
Thomas Moore
Thomas Moore

Reputation: 981

Pivot Table from Query involving Months in TeraData

I have a table generated from a query in TeraData that looks like this:

User_ID     Transaction_Month      Number_of_Visits
 123              1                       1
 123              2                       2
 221              4                       1
 123              5                       2
 221              3                       5

I am trying to create a Pivot table of sorts that will look like:

User_ID     Month_1    Month_2    Month_3  Month_4 ..... Month_12
 123

That will store the number of visits in each month column.

Any help would be greatly appreciated.

Thanks.

Upvotes: 0

Views: 129

Answers (1)

GMB
GMB

Reputation: 222402

You can do conditional aggregation:

select user_id,
    sum(case when transaction_month =  1 then number_of_visits else 0 end) month_1,
    sum(case when transaction_month =  2 then number_of_visits else 0 end) month_2,
    ...
    sum(case when transaction_month = 12 then number_of_visits else 0 end) month_12
from mytable
group by user_id

Upvotes: 1

Related Questions