Determine how many like sets of data in a table

Question

question

jrsanbornjr asked Nov 11, '21

Determine how many like sets of data in a table

Hi All,

I'm not exactly sure how to frame this question in query terms so I'm struggling to even do a proper Google search, but I suspect someone here could be enormously helpful.

Suppose I have two tables with a parent/child relationship, and what I need to be able to identify are the distinct child row sets that exist in the data. For example:

Parent table

[ID] [Name]

1, 'Set 1' | 2, 'Set 2' | 3, 'Set 3' | 4, 'Set 4'

Child table

[ID] [ParentID] [Letter]

1, 1, 'A' | 2, 1, 'B' | 3, 1, 'C' | 4, 2, 'A' | 5, 2, 'B' | 6, 2, 'C' | 7, 2, 'D' | 8, 2, 'E' | 9, 3, 'A' | 10, 3, 'B'| 11, 4, 'A' | 12, 4, 'B'

So from these 3 parent rows and 10 child rows, we can determine that there are three distinct sets of letters, [A, B, C], [A, B, C, D, E], and [A, B].

So assuming I had dozens of parent rows and hundreds of child rows of data with numerous duplicate sets, how would I construct a query that would give a summary showing the distinct sets of letters and how many of each?

Something like

[A, B, C] - 41

[A, B, C, D, E] - 32

[A, B] - 87

Thanks

grouping

2

10 |1200

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

Kev Riley ♦♦ commented · Nov 11, 2021 at 02:32 PM

What's the expected output for the example input you have given? Is it

[A, B, C] - 1

[A, B, C, D, E] - 1

[A, B] - 2

0 ·

jrsanbornjr Kev Riley ♦♦ commented · Nov 11, 2021 at 03:11 PM

Yes indeed

0 ·

Answer 1 · 2021-11-12T08:07:04Z

Kev Riley answered Nov 12, '21

declare @Parent table (id int, name varchar(50))
declare @Child table (id int, ParentID int, Letter char(1))

insert into @Parent (id, name)
values (1, 'Set 1' ),(2, 'Set 2'),(3, 'Set 3'),(4, 'Set 4')

insert into @Child (id, ParentID, Letter)
values (1, 1, 'A' ),( 2, 1, 'B' ),( 3, 1, 'C' ),( 4, 2, 'A' ),( 5, 2, 'B' ),( 6, 2, 'C' ),( 7, 2, 'D' ),( 8, 2, 'E' ),( 9, 3, 'A' ),( 10, 3, 'B'),( 11, 4, 'A' ),( 12, 4, 'B')

select
'['+LetterSets.LetterSet+']',
count(*)
from
(
select  distinct
child2.ParentID  ,
stuff ( 
( SELECT ',' + Letter 
  FROM
  @Child child1
  WHERE child1.ParentID = child2.ParentID
FOR XML PATH ( '' ) ) , 1 , 1 , '' ) as LetterSet
FROM
@Child child2
)LetterSets
group by LetterSets.LetterSet

-----------  ---
[A,B]        2
[A,B,C]      1
[A,B,C,D,E]  1

(3 rows affected)

1

10 |1200

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

jrsanbornjr commented · Nov 12, 2021 at 02:59 PM

Ah, thank you Kev. I had just briefly considered a concept along these lines but hadn't yet attempted to work it out. My first feeling was that it would be clunky and cumbersome and that there must be some "fancier", rarely used, grouping syntax that would be more "elegant". :P But your solution here works like a charm (and looks good, too!) Thanks again.

0 ·

question