Greetings!
I'm struggling today with something that I'm sure will be easy for the Tessitura smarties in the room! Can someone help point me in the right direction?
What I'm trying to pull is some customer data, then have a column for each season and if the customer has a subscription in that season put an X in that column.
I've written up this so far but I'm getting multiple rows per customer (see table)
select distinct ph.customer_no, case when season in (62) then 'x' else '' end as '14-15', case when season in (73) then 'x' else '' end as '15-16', case when season in (82) then 'x' else '' end as '16-17'
from T_PACKAGE_HISTORY ph join T_CUSTOMER c on ph.customer_no=c.customer_no
group by season, ph.customer_no
order by ph.customer_no
What am I missing that will get me just one row per customer number?
Thanks for your help!
Jenny
The reason you are getting multiple rows is because you are grouping by season
If you remove the season grouping and add a MAX around the case statements then you should get the result you are after, and SQL may be a little easier to understand than using the pivot statement.
select ph.customer_no, max(case when season in (62) then 'x' else '' end) as '14-15', max(case when season in (73) then 'x' else '' end) as '15-16', max(case when season in (82) then 'x' else '' end) as '16-17'
from T_PACKAGE_HISTORY phjoin T_CUSTOMER c on ph.customer_no=c.customer_nogroup by ph.customer_noorder by ph.customer_no
You might want to add a where season in (62,73,82) to it which will make the query run faster and remove all the people who have never had a package in those seasons.
Mark
I like this! Thanks, Mark! This worked perfectly.