# question

## 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

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

·

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 Likes 0 ·
Kev Riley ♦♦ ·
Yes indeed
0 Likes 0 ·

·
```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 comment

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

·
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 Likes 0 ·