Grouping by Customer and concating products opened in SQL-CodePudding

I have some data that I'm wanting to use to determine what products every customer has opened. With this data, I'm wanting to either create a new column or multiple Columns indicating these products. For example, consider the data below.

CustomerKey ProductType
6458         Checking
6458         Savings
6458         Savings
6461         Savings
6461          IRA

For customer 6458, I'd either like to have a products column that concat his/her products like one of the ways below.

Customer     Products               Checking/Savings         Savings/IRA
6458         Checking/Savings                1                     0
6461         Savings/IRA                     0                     1

Is there anything I can use besides min/max around product type that will concat all of the members?

Select Customerkey, producttype
from share
group by customerkey

CodePudding user response：

The logic for product combinations would require you to hard-code each possible combination into a conditional sum as below:

with p as (
    select distinct customerkey customer, Stuff(Products,1,1,'') Products
    from t
    cross apply (
        select distinct '/'   ProductType
        from t t2
        where t2.customerkey=t.customerkey
        for xml path('')
    )x(Products)
)
select *,
    max(case when products='Checking/Savings' then 1 else 0 end) as [Checking/Savings],
    max(case when products='IRA/Savings' then 1 else 0 end) as [IRA/Savings]
from p
group by customer, products

CodePudding user response：

I would do something like this. This is using STRING_AGG to generate the products list. And then some conditional aggregation across the known product types to have that column return a 1 or 0.

create table #Something
(
    CustomerKey int
    , ProductType varchar(20)
)
insert #Something
select 6458, 'Checking' union all
select 6458, 'Savings' union all
select 6458, 'Savings' union all
select 6461, 'Savings' union all
select 6461, 'IRA'
;

--using a cte to ensure we get only distinct ProductTypes
with PreAggregate as
(
    select distinct CustomerKey
        , ProductType
    from #Something
)

select s.CustomerKey
    , Products = STRING_AGG(ProductType, '/') within group(order by ProductType)
    , Checking = max(case when ProductType = 'Checking' then 1 else 0 end)
    , Savings = max(case when ProductType = 'Savings' then 1 else 0 end)
    , IRA = max(case when ProductType = 'IRA' then 1 else 0 end)
from PreAggregate s
group by s.CustomerKey

drop table #Something