Grouping by Customer and concating products opened in SQL

Question

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

If you have access to a later version of SQL server you can use `STRING_AGG` — Peter Smith, Nov 10 '21 at 21:51
What's the logic for the other two columns? Eg what happens of a customer has checking and IRA? — Stu, Nov 10 '21 at 21:51
Those output columns make no sense. Why do you need to name the columns with the contents of the Products output? Why not just have a column for each ProductType? — Sean Lange, Nov 10 '21 at 21:58
It would be the same as the others. It would either fall under products as "Checking/IRA" or I would create a column called Checking/IRA with an Indicator of 1. I would need to do columns for Checking/IRA/Savings, etc. I trimmed it down to make it a little easier. Unfortunately, I tried String_AGG and it didn't work. I wish my company would let me use MYSQl so I could use groupconcat — user14316330, Nov 10 '21 at 22:03
Doing dynamic columns like that means you would have to use dynamic sql and it is a big red flag that something has gone wrong in the design here. — Sean Lange, Nov 10 '21 at 22:04
Having columns for each "maybe" combination of products is a serious anti-pattern - what happens if there are 10 products...? I would re-think this. The `Products` column is pretty easy. — Stu, Nov 10 '21 at 22:06
Basically, my boss wants me to do some research on dormant members based on what products they have open (yes, he wants them segmented as I mentioned above) and wants me to look at balances, how many products they have (which I know I can do without the segments, years account open, etc). Everything he wants I can easily do with basic SQL or python code, but he specifically wants them grouped and segmented as I mentioned. Not saying it's a great idea by any means, but I'm certainly open to suggestions if there's a better way to go about it. — user14316330, Nov 10 '21 at 22:09
I would actually prefer just the products column but listed the other as an alternative option. — user14316330, Nov 10 '21 at 22:13
@user14316330 Realised you can use conditional aggregation provided you are prepared to hard-code the list of possible columns. — Stu, Nov 10 '21 at 22:14
Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Charlieface, Nov 11 '21 at 01:01

Stu · Answer 1 · 2021-11-10T22:13:58.043

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

score 1 · Accepted Answer · answered Nov 10 '21 at 22:15

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

Grouping by Customer and concating products opened in SQL

2 Answers2