Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  RecursiveGroup

Author  Topic  Vivan Starting Member 9 Posts Posted - 2015-04-24 : 23:33:01 --May i get help to design query for below scenario if possible. /* The resultset is based on three criteria 1st: GROUP BY PART TYPE,MAKE,MODEL,YEAR Based on above group whatever SKU we will get those all SKU should present with all available remarks condition: if ONLY 1 Remarks 1 present in the group then it should exclude from the output. 2nd: Based on the group (PART TYPE,MAKE,MODEL,YEAR) if All Remarks 3 should also available with all Remarks 1 if not then exclude from the output. 3rd: even if any one SKU or remarks 3 of a group not fullfilling the crieteria then entire Group need to be exclude, not only that line. */ DECLARE @MYTABLE TABLE ( [PART TYPE] VARCHAR (50), [MAKE] VARCHAR (50), [MODEL] VARCHAR (50), [YEAR] VARCHAR (50), [SKU] VARCHAR (50), [REMARKS] VARCHAR (50), [REMARKS3] VARCHAR (50) ) INSERT @MYTABLE --[PART TYPE], [MAKE], [MODEL], [YEAR], [SKU], [REMARKS], [REMARKS3] -- set 1. BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Four Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Four Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Four Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Rear Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Rear Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Rear Wheel Drive', 'Front' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Four Wheel Drive', 'Rear' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Four Wheel Drive', 'Rear' union all SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Four Wheel Drive', 'Rear' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Rear Wheel Drive', 'Rear' union all SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Rear Wheel Drive', 'Rear' union all SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Rear Wheel Drive', 'Rear' UNION ALL --set 2 BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS SELECT 'CONV', '89', '668', '2014', 'TFC82112', 'Four Wheel Drive', 'Front' union all SELECT 'CONV', '89', '668', '2014', 'TFC82250', 'Four Wheel Drive', 'Front' union all SELECT 'CONV', '89', '668', '2014', 'TFC82350', 'Four Wheel Drive', 'Front' union all SELECT 'CONV', '89', '668', '2014', 'TFC82112', 'Rear Wheel Drive', 'Front' union all SELECT 'CONV', '89', '668', '2014', 'TFC82250', 'Rear Wheel Drive', 'Front' union all SELECT 'CONV', '89', '668', '2014', 'TFC82350', 'Rear Wheel Drive', 'Front' UNION ALL --SET 3 SELECT 'AXLE', '894', '888','1984','MOOK8620', 'Four Wheel Drive', 'Front' union all --EXLUDE, ONLY 1 Remarks 1 present in the group --SET 4 Even All Part Present with All Remarks but All Remarks 3 not Present with all Remarks 1,- Full Group Exclude SELECT 'CLTPREPLAT', '72', '945', '2008', 'TCI498911', 'All Wheel Drive', 'FRONT' union all SELECT 'CLTPREPLAT', '72', '945', '2008', 'TCI498911', 'FRONT Wheel Drive', 'REAR' union all SELECT 'CLTPREPLAT', '72', '945', '2008', 'WLW140', 'All Wheel Drive', 'REAR' union all SELECT 'CLTPREPLAT', '72', '945', '2008', 'WLW140', 'FRONT Wheel Drive', 'FRONT' union all -- SET 5 Exclude from Resultset, as all part not with all remarks 1 SELECT 'BALLJOINT', '96', '949', '2012', 'PRS599', 'Rear Wheel Drive', 'FRONT' union all SELECT 'BALLJOINT', '96', '949', '2012', 'PRS599', 'Front Wheel Drive','FRONT' union all SELECT 'BALLJOINT', '96', '949', '2012', 'OMEFK36', 'Rear Wheel Drive','FRONT' so output will be set 1 and set 2 SELECT * FROM @MYTABLE Thanks viva