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