-2

Kindly help me to get the list of similar groups together.

Fig1

Please see the above illustration.

Here, BaseItem 6586, 6587, 6588, 6589 are sharing similar kind of SubItems (SubSet 1, SubSet2, SubSet3 & SubSet4 respectively).

Also, BaseItem 14 & 80 are sharing similar SubItems(SubSet 5 & SubSet 8).

At the same time, BaseItem 7000, 7010 are not sharing similar SubItems, because their subsets have either more or fewer SubItems.

So What I need is to identify and group similar Subsets together and have a unique identification for that.

fig2

See Script below with data.

CREATE TABLE Table1 ([BaseItem] int, [SubItem] int, primary key (BaseItem, SubItem)) ;

INSERT INTO Table1
    ([BaseItem], [SubItem])
VALUES
    (6586, 3913),
    (6586, 7460),
    (6586, 15659),
    (6587, 3913),
    (6587, 7460),
    (6587, 15659),
    (6588, 3913),
    (6588, 7460),
    (6588, 15659),
    (6589, 3913),
    (6589, 7460),
    (6589, 15659),
    (14, 2459),
    (14, 2460),
    (7000, 3913),
    (7000, 7460),
    (7000, 15659),
    (7000, 1256),
    (7010, 3913),
    (7010, 7460),
    (80, 2459),
    (80, 2460),
    (200, 2459),
    (200, 2460),
    (200, 2870),
    (200, 1951);

For better clarity, please see all the details.

I have 2 Tables,

Equipments
-----------------
Printer a
Printer b
Printer c
Printer d
Printer e
Printer f
Printer g

Accessories
-------------
Accessory a
Accessory b
Accessory c
Accessory d
Accessory e
Accessory f

In order to show the compatible Accessories for each Printer, I have combined both the tables and produced another view. It will give the results as listed below:

Equipments       |    Accessories   
--------------------------------------------
Printer a             Accessory b
Printer a             Accessory c
Printer a             Accessory e

Printer b             Accessory a
Printer b             Accessory b

Printer c             Accessory b
Printer c             Accessory c

Printer d             Accessory a
Printer d             Accessory b

Printer e             Accessory b
Printer e             Accessory c
Printer e             Accessory e

Printer f             Accessory d
Printer f             Accessory e

Printer g             Accessory a
Printer g             Accessory b

Final result must be like:

ID  |     Equipments       |    Accessories   
--------------------------------------------
1         Printer a             Accessory b
1         Printer a             Accessory c
1         Printer a             Accessory e

1         Printer e             Accessory b
1         Printer e             Accessory c
1         Printer e             Accessory e

2         Printer b             Accessory a
2         Printer b             Accessory b

2         Printer d             Accessory a
2         Printer d             Accessory b

2         Printer g             Accessory a
2         Printer g             Accessory b

3         Printer c             Accessory b
3         Printer c             Accessory c

4         Printer f             Accessory d
4         Printer f             Accessory e
  • I've improved formatting for you but please consider editing further by making your description consistent, regarding column names and table names. – Andriy M Nov 2 '15 at 8:54
  • actually I am first time here... sorry & bear with me for my poor expression – Tech_IQ Nov 2 '15 at 8:56
1

Edited version, this one will fetch only groups that match at least one other group (=all same items exist), this assumes the items in the groups are unique.

select *
from Table1 T1
outer apply (
  select count(*) as C from Table1 T2 where T1.BaseItem = T2.BaseItem
) C1
outer apply (
  select top 1
  row_number() over (partition by T3.BaseItem, T4.BaseItem order by T3.SubItem) as RN,
  T4.BaseItem
  from Table1 T3 
  join Table1 T4 on T3.BaseItem != T4.BaseItem
  and T3.SubItem = T4.SubItem
  and T3.BaseItem = T1.BaseItem
  order by RN desc
) C2
outer apply (
  select count(*) as C from Table1 T5 where T5.BaseItem = C2.BaseItem
) C3
where C1.C = C2.RN AND
C1.C = C3.C

Example in SQL Fiddle

  • It's still really bad. You need to exclude BaseItems 7000 & 7010? Just add T1.BaseItem not in (7000, 7010) – James Z Nov 1 '15 at 15:21
  • Maybe this version is now better. If not, please write your question so that it describes your problem not the group numbers you want to do something with – James Z Nov 1 '15 at 15:42
  • Thanks for your precious time. I have modified my Question again. – Tech_IQ Nov 2 '15 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.