CODEWITHSUNDEEP
sql-server
Mette
Mette

Reputation: 39

Prioritized client segments

My customers own products from different product groups. Example:

Client  Product group
1       All-in-one
1       Senior
2       All-in-one
2       Other
3       Senior
3       Other
4       Other

The product groups are prioritized, so that if you own products from both the all-in-one and the senior product group you would be classified over all as an All-in-one customer.

The product groups prioritized are:

  1. All-in-one
  2. Senior
  3. Other

I would like a view where each customer only appear once like:

Client  Product group
1       All-in-one
2       All-in-one
3       Senior
4       Other

Can this be done in a single step without a ton of views?

Upvotes: 1

Views: 37

Answers (2)

gofr1
gofr1

Reputation: 15987

Try this (you dont need use clients_groups cte because you have this table already):

;WITH clients_groups AS (
SELECT *
FROM (VALUES
(1, 'All-in-one'),
(1, 'Senior'),
(2, 'All-in-one'),
(2, 'Other'),
(3, 'Senior'),
(3, 'Other'),
(4, 'Other')) as cg (Client, Product_group)
),
final AS (
SELECT ROW_NUMBER() OVER(PARTITION BY Client ORDER BY id Asc) AS R,g.Client, p.Name 
FROM clients_groups g
LEFT JOIN (VALUES
(1, 'All-in-one'),
(2, 'Senior'),
(3, 'Other')) as p(id, name) ON g.Product_group = p.name)

SELECT Client,Name
FROM final 
WHERE R = 1

Results:

Client      Name
----------- ----------
1           All-in-one
2           All-in-one
3           Senior
4           Other

(4 row(s) affected)

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28930

First one works if only those product groups remain,second one you have flexibilty to order but uses ctes

select id,min(pg) from #client
group by id

;with cte
as
(
select 
id,
min(case when pg='All-in-one'  then 1
 when pg='senior' then 2
 when pg='other' then 3
 end ) pg
 from 
 #client
 group by id
 )
 select id,
 case pg when 1 then 'All-in-one'
  when 2 then 'Senior'
  when 3 then 'other'
 end as 'PG'
 from cte

Upvotes: 1

Related Questions