Hi,
Using a user defined function for listsplitter to manage the
comma separated list argument. When used along with aggregate function,
it shows message as 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'
Sample code below
Declare @specTable table(prodID int,prodName varchar(30),specName varchar(100), specValue varchar(100))
Insert into @specTable values
(1,'Sink1','Shape','Round')
,(1,'Sink1','Color','White')
,(1,'Sink1','Style','Pedestal')
,(2,'Sink2','Shape','Square')
,(2,'Sink2','Color','Black')
,(2,'Sink2','Style','Wallmount')
,(3,'Sink3','Shape','Round')
,(3,'Sink3','Color','White')
,(3,'Sink3','Style','Pedestal')
,(4,'Sink4','Shape','Rectangle')
,(4,'Sink4','Color','White')
,(4,'Sink4','Style','Pedestal')
,(5,'Sink5','Shape','Round')
,(5,'Sink5','Color','green')
,(5,'Sink5','Style','Pedestal')
Select * from @specTable
select prodid
from @spectable
group by prodid
having sum(case when specname='Color' and specvalue='White' then 1 else 0 end)>0
and sum(case when specname='Shape' and specvalue in
( select value from ListSplitter('Round,Rectangle'))
then 1 else 0 end)>0