Group multiple lines

Question

This is my table:

BOX ITEM 1 0000001 1 0000002 1 0000003 1 0000004 2 1111111 2 1111111 2 1111111 2 1111111 3 0000001 3 0000002 3 0000003 3 0000004 4 0000001 4 0000002 4 0000003 

It means:

• In block 1 there are 4 different elements.
• In block 2 there are 4 elements.
• Box 3 has 4 elements.
• In block 4 there are 3 elements.

Attention!

• Box 1 and box 3 contain exactly the same elements, so we can group them.
• Box 4 contains almost the same as box 1 and box 3, but is actually not the same, so we cannot group box 1 and 3.

Now the question is:

I want to group all the boxes with the same content, having a count of the number of identical fields, for example, but any other approach is valid, this is just an example to show my needs:

 QUANTITY ITEM BOX 2 0000001 1, 3 2 0000002 1, 3 2 0000003 1, 3 2 0000004 1, 3 1 1111111 2 1 1111111 2 1 1111111 2 1 1111111 2 1 0000001 4 1 0000002 4 1 0000003 4 

(This means that I have 2 mailboxes with elements 0000001, 0000002, 0000003 and 0000004, which are field 1 and field 3.

I have 1 box with elements 111111, 111111 ... etc. etc.)

And I can’t find a way to do this ... Any help?

Answer 1

Assuming this is MySQL, try:

 select count(distinct box) quantity, items, group_concat(box) boxes from (select box, group_concat(item) items from my_table group by box) v group by items 

Answer 2

If you are running SQL Server, you need to use something like this for group_concat mySQL http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist . MySQL's solution and one of them suggests that the order of the elements in each field is always the same or that the elements must be sorted before they are combined so that you can compare the contents of each window. It ends up looking pretty dirty with all the nesting, but it gives the desired result:

 SELECT count(box) NumBoxes, list Contents, BoxList = substring((SELECT ( ', ' + cast(box as varchar) ) FROM (SELECT b1.box, List = substring((SELECT ( ', ' + item ) FROM boxes b2 WHERE b1.box = b2.box ORDER BY box, item FOR XML PATH( '' ) ), 3, 1000 ) FROM boxes b1 GROUP BY box ) source2 WHERE source1.list = source2.list ORDER BY box, list FOR XML Path( '' ) ), 3, 1000 ) FROM(SELECT b1.box, List = substring((SELECT ( ', ' + item ) FROM boxes b2 WHERE b1.box = b2.box ORDER BY box, item FOR XML PATH( '' ) ), 3, 1000 ) FROM boxes b1 GROUP BY box ) source1 GROUP BY list 

By the way, I used this table for testing, and the fact that BOX was an INT data type required transfer in the third line of code above:

 create table boxes( BOX int null, ITEM char(7) null) go insert into boxes values(1,'0000001'), (1,'0000002'), (1,'0000003'), (1,'0000004'), (2,'1111111'), (2,'1111111'), (2,'1111111'), (2,'1111111'), (3,'0000001'), (3,'0000002'), (3,'0000003'), (3,'0000004'), (4,'0000001'), (4,'0000002'), (4,'0000003') go