question

Binod avatar image
Binod asked

Grouping Dilemma

declare @YourTable table ([Part Type] varchar(50), [make] varchar(50), [model] varchar(50), [YEAR] varchar(50), [REMARKS] varchar(50), [Number] varchar(50)) -- SET 1 insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN1234' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN1234' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN4567' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN4567' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN8910' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN8910' --SET 2 insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN5555' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN5555' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN9999' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN9999' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN7777' /* For any Year, make, model / Part Type There will be n number of parts have x number of remarks. if n number of parts in a group of Year, make and model and part type contains 2 remarks then it sshould be in output only when those 2 remarks present with all n number. SET 1 Should appear in output because 2014 Cadillac CTS has 2 drive types available and all 3 numbers (MCN1234,MCN4567,MCN8910)for FLRLNR. SET 2 Should not appear in output because 'MCN7777' is not available with ALL WHEEL DRIVE, so entire group discarded. */
sql-server-2005group-by
groupdellima.txt (2.8 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It might help to explain your question if you gave some example data and expected output - I'm not sure from the question which rows you want, which you don't and why?
0 Likes 0 ·
Hi Kev, Greetings, i have modified my question with attachment for more clarity, Please let me know if any concern. Thank You Very much Kev. :)
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
This feels like a relational division type problem. The best sources for this are: - Joe Celko : https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ - Peter Larsson : http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx Using those as a guide, I've come up with this solution: declare @YourTable table (PT varchar(50), MK varchar(50), MO varchar(50), YR varchar(50), DVR varchar(50), CC varchar(50), SK varchar(50)) insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','All Wheel Drive','RAY','RAYATD536C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','RAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1086H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','All Wheel Drive','RAY','RAYATD536C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','JAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1086H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','JAYEHT1089H'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383194'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383195'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383382'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383382'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750325'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750325'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750326'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750326'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750190'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750190'; select YourTable2.* from @YourTable Yourtable join ( select PT, MK, MO, YR, count(*) i from ( select distinct PT, MK, MO, YR, dvr from @YourTable ) a group by PT, MK, MO, YR )b on b.PT = Yourtable.PT and b.MK = Yourtable.MK and b.MO = Yourtable.MO and b.YR = Yourtable.YR join ( select SK, count(*) i from ( select distinct SK, dvr from @YourTable ) a group by SK )c on c.SK = Yourtable.SK and b.i > c.i right join @YourTable Yourtable2 on b.PT = Yourtable2.PT and b.MK = Yourtable2.MK and b.MO = Yourtable2.MO and b.YR = Yourtable2.YR where Yourtable.SK is null I'd suggest digesting those links to understand what's going on here
12 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Awesome code and i am Speechless...... Thank You Very Much Kev, shall i do testing with some more data. Thanks Again, Trust me its awesome, and i learn a lot.
0 Likes 0 ·
Can you add that example data to the question, along with the expected output - it makes it easier to understand then
0 Likes 0 ·
@Binod : I'm confused. Is this an addition to the original question or a change in the requirements. At first you wanted to exclude the entire PT if any SK within it didn't have all the DVRs, but now you only want to exclude the SK?
0 Likes 0 ·
Please can you update the example output above - I am still struggling to understand what you require
0 Likes 0 ·
@Binod glad I could help
0 Likes 0 ·
Show more comments
kiranmath avatar image
kiranmath answered
Binod: If you post the sql statement to populate the same data and tabular o/p you want it might be easier for us to understand the requirement.. ~Kiran
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.