Ad

Stored Procedure To Get Cutomer Ids On The Basis Of Comma Seperated Value Passed

Items Table:

iditem_namecodecust_id
1Mango111u1
2Milk112u2
3Chocolate113u3
4Milk112u1
5Mango111u2
6Berry114u3
7Chocolate113u1
8Berry114u2
9Ice-cream114u3
10Mango111u4

These is the customer table and I have to write an stored procedure in SQL Server to find the cust_id who have buy all items passed in comma separated values like 'Mango,Milk,Chocolate'

CREATE PROCEDURE Items_find
    @items_value nvarchar(max)
AS
BEGIN
END

EXEC Items_find 'Mango,Milk,Chocolate'  
EXEC Items_find 'Mango,Milk'

Stored procedure should return cust_id = u1 if 'Mango,Milk' then output should be cust_id=u1, u2 or if 'Mango' then output is cust_id=u1, u2, u3

I have tried to filter out the cust_id w.r.t. to the count of items passed in comma-separated but after that I am not able find the cust_id who buys specifically these items

Ad

Answer

One solution is to count first how many words are in your search,
and then select all rows that contain your search and use the count to keep only those with the same group by count

Here is an example

declare @Items table (id int, item_name varchar(50), code int, cust_id varchar(10))
insert into @Items values (1, 'Mango', 111, 'u1'),
(2, 'Milk', 112, 'u2'), (3, 'Chocolate', 113, 'u3'), (4, 'Milk', 112, 'u1'), (5, 'Mango', 111, 'u2'),
(6, 'Berry', 114, 'u3'), (7, 'Chocolate', 113, 'u1'), (8, 'Berry', 114, 'u2'), (9, 'Ice-cream', 114, 'u3'),
(10, 'Mango', 111, 'u4')
declare @search varchar(50) = 'Mango,Milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', '')) + 1)

select cust_id
from   @Items
where  @search like '%' + item_name + '%'
group by cust_id
having count(1) = @cnt

this will return

cust_id
u1
u2

And if you want it in one string, you can use string_agg for that

declare @search varchar(50) = 'Mango,milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', '')) + 1)

select 'cust_id = ' +
( select string_agg(t.cust_id, ', ') 
          from   ( select top 100000
                          i.cust_id
                   from   @Items i
                   where @search like '%' + i.item_name + '%'
                   group by i.cust_id
                   having count(1) = @cnt
                  ) t
 ) as Result

this will return

cust_id = u1, u2
Ad
source: stackoverflow.com
Ad