• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 176 views
  • Users 0 members are here
Related

Subscriber data pull in SSMS

Jenny Carvo
$organization

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. 

Customer no 14/15 15/16 16/17
123 X X

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

customer no 14-15 15-16 16-17
100 x
100 x
100 x

What am I missing that will get me just one row per customer number?

Thanks for your help!

Jenny

Parents
  • $organization

    Hi Jenny,

    For this to produce 1 row per customer, you'll need to pivot the data - try using the code below and see how you get on!

    Martin

    Select * from
    (
    select distinct
    customer_no,
    case when season = 62 then '14-15'
    when season = 73 then '15-16'
    when season = 82 then '16-17' end as season,
    case when season = 62 then 'X'
    when season = 73 then 'X'
    when season = 82 then 'X' end as has_season
    from T_PACKAGE_HISTORY ph
    where ph.season in (62,73,82)
    ) a
    pivot

    (max(has_season)
    for season in
    ([14-15]
    ,[15-16]
    ,[16-17])) as b

Reply
  • $organization

    Hi Jenny,

    For this to produce 1 row per customer, you'll need to pivot the data - try using the code below and see how you get on!

    Martin

    Select * from
    (
    select distinct
    customer_no,
    case when season = 62 then '14-15'
    when season = 73 then '15-16'
    when season = 82 then '16-17' end as season,
    case when season = 62 then 'X'
    when season = 73 then 'X'
    when season = 82 then 'X' end as has_season
    from T_PACKAGE_HISTORY ph
    where ph.season in (62,73,82)
    ) a
    pivot

    (max(has_season)
    for season in
    ([14-15]
    ,[15-16]
    ,[16-17])) as b

Children
No Data