Grouping and exclusion

Transact-SQL
1

Hello Techie,
may anyone please help me on this.

/*
for a group of product,brand,modelno and year if a Distinct SKU contain only one distinct mountingtype
then entire group exclude from output

*/
declare @t1 table
(
product varchar (50),
brand varchar (50),
modelno varchar (50),
year varchar (10),
SKU varchar (50),
mountingtype varchar (50),
TerminationType varchar (50)

)

Insert @t1

SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'EIB5.86350K', 'Surface Mount', 'topload' UNION ALL
SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'EIB5.86350K', 'solder', 'topload' UNION ALL
SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'WLW140-12824', 'Surface Mount', 'topload' UNION ALL
SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'WLW140-12824', 'solder', 'topload' UNION ALL
SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'XYZ140-12824', 'Surface Mount', 'topload' UNION ALL
SELECT 'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'XYZ140-12824', 'solder', 'topload' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13122','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13122','solder','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13128','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13128','solder','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13130','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','13130','solder','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','81250','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','82250','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','R300','Surface Mount','' UNION ALL
SELECT 'Foil resistor','FOIL','F-150','1976','R3120','Surface Mount',''

-- Expected Output
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'EIB5.86350K', 'Surface Mount', 'topload'
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'EIB5.86350K', 'solder', 'topload'
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'WLW140-12824', 'Surface Mount', 'topload'
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'WLW140-12824', 'solder', 'topload'
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'XYZ140-12824', 'Surface Mount', 'topload'
'TANTALUM CAPACITOR', 'TTC', 'POWERCAP', '2007', 'XYZ140-12824', 'solder', 'topload'

Thanks

2

why are EIB5.86350K is in the result ?, it contains 2 mountingtype

3

Maybe this:

select b.*
  from (select product
              ,brand
              ,modelno
              ,[year]
              ,SKU
          from @t1
         group by product
                 ,brand
                 ,modelno
                 ,[year]
                 ,SKU
         having count(mountingtype)>1
       ) as a
       inner join @t1 as b
               on b.product=a.product
              and b.brand=a.brand
              and b.modelno=a.modelno
              and b.[year]=a.[year]
              and b.SKU=a.SKU
1 Like