Subscriber data pull in SSMS

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

Jenny,

If you wanted to do it all in one query, you would probably need to employ some form of pivot in there (I avoid using pivots; they never seem to work exactly the way I want them, and they are often difficult enough for me to get correct in the first place).

That said, if all the columns that you are putting out to the side are known and not too many (e.g. just those last three seasons versus a potential for 20+ seasons), it should be easy enough to use the query you have already, throwing that first query into a temporary table, and then using coalesce and a group by on that information from the temporary table to get you what you want.  You would need to rewrite the case statement to return a null rather than '' and it might help to rewrite '14-15' as variables for the temporary table, but that should work.

Maybe something like this:

SELECT DISTINCT    ph.customer_no,
                CASE WHEN season IN (62) THEN 'x' ELSE NULL END AS y15,
                CASE WHEN season IN (73) THEN 'x' ELSE NULL END AS y16,
                CASE WHEN season IN (82) THEN 'x' ELSE NULL END AS y17
                INTO #temp
FROM        T_PACKAGE_HISTORY ph
    JOIN    T_CUSTOMER c ON ph.customer_no = c.customer_no
GROUP BY    ph.season,
            ph.customer_no
ORDER BY    ph.customer_no

SELECT    a.customer_no,
        MAX(COALESCE(y15,'')) AS '14-15',
        MAX(COALESCE(y16,'')) AS '15-16',
        MAX(COALESCE(y17,'')) AS '16-17'
FROM #temp a
GROUP BY    a.customer_no
ORDER BY    a.customer_no

Let me know if that helps.

John

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 ph
join T_CUSTOMER c on ph.customer_no=c.customer_no
group by ph.customer_no
order 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.

Thanks, Martin and John.  I'll have a play around with these, too.  It's great to have the multiple options to get this done.