/*
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 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]
SELECT 'ADDALEAF',	     '54',	'667',	    '2010',	'SKYF35G',  'Four Wheel Drive',	'Front' union all -- 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',	'SKYF46D',	'Four Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Front' union all-- 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',	'Rear' union all-- 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',	'SKYF46D',	'Four Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Rear' union all-- 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',	'Rear Wheel Drive',	'Rear' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Rear' UNION ALL-- 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-- 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',	'TFC82250',	'Four Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82250',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Rear Wheel Drive',	'Front' UNION ALL-- 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 'AXLE',	'894', '888','1984','MOOK8620',	'Four Wheel Drive',	'Front' union all	--EXLUDE, ONLY 1 Remarks 1 present in the group

SELECT 'CLTPREPLAT',	'72',	'945',	'2008',	'TCI498911',	'All Wheel Drive',	    'FRONT'  union all	 --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',	'FRONT Wheel Drive',	'REAR' union all	 --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',	'WLW140',	    'All Wheel Drive',	    'REAR' union all	     -- 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',	'WLW140',	    'FRONT Wheel Drive',	'FRONT' union all	-- Even All Part Present with All Remarks but  All Remarks 3 not Present with all Remarks 1,- Full Group Exclude

SELECT 'BALLJOINT',	'96',	'949',	'2012',	'PRS599',	'Rear Wheel Drive',	'FRONT'   union all	--Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'PRS599',	'Front Wheel Drive','FRONT'   union all  --	Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'OMEFK36',	'Rear Wheel Drive','FRONT'   	        --Exclude from Resultset, as all part not with all remarks 1


SELECT * FROM @MYTABLE

Thanks Techie

• Edited by Pras05 Friday, April 24, 2015 6:40 PM

Pardon Me Erland.  i was really confused. Please allow me to Explain the scenario from beginning.

/*
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]
SELECT 'ADDALEAF',	     '54',	'667',	    '2010',	'SKYF35G',  'Four Wheel Drive',	'Front' union all -- 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',	'SKYF46D',	'Four Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Front' union all-- 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',	'Rear' union all-- 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',	'SKYF46D',	'Four Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Rear' union all-- 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',	'Rear Wheel Drive',	'Rear' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Rear' UNION ALL-- 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-- 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',	'TFC82250',	'Four Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82250',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Rear Wheel Drive',	'Front' UNION ALL-- 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 'AXLE',	'894', '888','1984','MOOK8620',	'Four Wheel Drive',	'Front' union all	--EXLUDE, ONLY 1 Remarks 1 present in the group

SELECT 'CLTPREPLAT',	'72',	'945',	'2008',	'TCI498911',	'All Wheel Drive',	    'FRONT'  union all	 --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',	'FRONT Wheel Drive',	'REAR' union all	 --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',	'WLW140',	    'All Wheel Drive',	    'REAR' union all	     -- 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',	'WLW140',	    'FRONT Wheel Drive',	'FRONT' union all	-- Even All Part Present with All Remarks but  All Remarks 3 not Present with all Remarks 1,- Full Group Exclude

SELECT 'BALLJOINT',	'96',	'949',	'2012',	'PRS599',	'Rear Wheel Drive',	'FRONT'   union all	--Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'PRS599',	'Front Wheel Drive','FRONT'   union all  --	Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'OMEFK36',	'Rear Wheel Drive','FRONT'   	        --Exclude from Resultset, as all part not with all remarks 1


SELECT * FROM @MYTABLE

Thank You Very Much 

• Edited by Pras05 Saturday, April 25, 2015 2:35 AM

Hi Visakh,

Please help me on this.

/*
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]
SELECT 'ADDALEAF',	     '54',	'667',	    '2010',	'SKYF35G',  'Four Wheel Drive',	'Front' union all -- 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',	'SKYF46D',	'Four Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Front' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Front' union all-- 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',	'Rear' union all-- 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',	'SKYF46D',	'Four Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Four Wheel Drive',	'Rear' union all-- 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',	'Rear Wheel Drive',	'Rear' union all-- 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',	'SKYF46D',	'Rear Wheel Drive',	'Rear' union all-- 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',	'AIR75620',	'Rear Wheel Drive',	'Rear' UNION ALL-- 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-- 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',	'TFC82250',	'Four Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Four Wheel Drive',	'Front' union all-- 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',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82250',	'Rear Wheel Drive',	'Front' union all-- 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',	'TFC82350',	'Rear Wheel Drive',	'Front' UNION ALL-- 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 'AXLE',	'894', '888','1984','MOOK8620',	'Four Wheel Drive',	'Front' union all	--EXLUDE, ONLY 1 Remarks 1 present in the group

SELECT 'CLTPREPLAT',	'72',	'945',	'2008',	'TCI498911',	'All Wheel Drive',	    'FRONT'  union all	 --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',	'FRONT Wheel Drive',	'REAR' union all	 --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',	'WLW140',	    'All Wheel Drive',	    'REAR' union all	     -- 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',	'WLW140',	    'FRONT Wheel Drive',	'FRONT' union all	-- Even All Part Present with All Remarks but  All Remarks 3 not Present with all Remarks 1,- Full Group Exclude

SELECT 'BALLJOINT',	'96',	'949',	'2012',	'PRS599',	'Rear Wheel Drive',	'FRONT'   union all	--Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'PRS599',	'Front Wheel Drive','FRONT'   union all  --	Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96',	'949',	'2012',	'OMEFK36',	'Rear Wheel Drive','FRONT'   	        --Exclude from Resultset, as all part not with all remarks 1


SELECT * FROM @MYTABLE

your last suggestion is working absolutely fine only 1 enhancement from the last scenario i.e All Remarks 3 should also contain All Remarks 1.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/111ce0db-8803-405e-8886-4cdb60d5c4ef/grouping-checks-all-element-from-a-group-present-with-all-option-available-in-group?forum=transactsql

Thank You Very Much

• Edited by Pras05 Saturday, April 25, 2015 3:28 AM

Tough to try and help here.  Multiple posts, edits, deletions, and confusing descriptions, including code blocks that contain narrative too, and quoting other people where we can't tell if it's you answering their requests for clarification, or you asking a new question, or both.

You did better than many people by posting sample data in an executable query, so good job there.   Mixing code blocks and quotations together though, not so good.  Speaking for myself, I can't even tell which query is now the test data to  start with.

I certainly don't want to suggest creating another post, since this already is a repeat.  All I can say, is for future posts on future and different topics, continue posting the SQL sample data, but avoid multiple posts, use code blocks just for code, quotation blocks just for quotations, and people here will be happy to try and help.