T-SQL (SQL Server): Extract unique sets/groups of data

Question

Using T-SQL, how would I extract the unique sets from a table using SQL. Here is a sample table:

ID  Value
1   A
1   B
1   C
2   A
2   B
2   C
3   D
3   E
4   D
4   E
5   X
5   Y
5   Z
6   Q
7   Q
8   Q
9   Q
10  A

In this case, the desired result set would have five unique sets as follows:

A
B
C

D
E

X
Y
Z

Q

A

This is representative of incoming data that I must transform. The incoming data has hundreds of repetitive sets like this. There is substantial value in identifying these sets; putting them into a separate table; assigning them a SetID; and then associating the core data to the relevant set based on a SetID JOIN; etc.

Tag your question with the database you are using. And what would the sets look like if you had, say, "10 A"? — Gordon Linoff, Jun 17 '18 at 13:30

score 1 · Answer 1 · answered Jun 17 '18 at 14:37

If you don't have 2017 you can use stuff with xml path. for example

drop table t
go
create table t(ID  int ,Value varchar(1));
go
insert into t values
(1 ,  'A'),
(1 ,  'B'),
(1 ,  'C'),
(2 ,  'A'),
(2 ,  'B'),
(2 ,  'C'),
(3 ,  'D'),
(3 ,  'E'),
(4 ,  'D'),
(4 ,  'E'),
(5 ,  'X'),
(5 ,  'Y'),
(5 ,  'Z'),
(6 ,  'Q'),
(7 ,  'Q'),
(8 ,  'Q'),
(9 ,  'Q'),
(10,  'A');

To get a rows of sets

select  distinct 
        maskdetail = STUFF((
          SELECT ',' + t1.value
          FROM t t1
          WHERE t1.id = t.id
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from t

maskdetail
------------------------------
A
A,B,C
D,E
Q
X,Y,Z

(5 row(s) affected)

To get a column of sets

select t.value 
from t 
join
(
select  min(id) minid,maskdetail
from
(
select  distinct t.id,
        maskdetail = STUFF((
          SELECT ',' + t1.value
          FROM t t1
          WHERE t1.id = t.id
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from t
) s
group by maskdetail
) x
on x.minid = t.id

value
-----
A
A
B
C
D
E
Q
X
Y
Z

(10 row(s) affected)

Well, since you already mentioned MSSQL 2017, then might as well show how to do this via STRING_AGG ;) — LukStorms, Jun 17 '18 at 14:47

score 0 · Answer 2 · answered Jun 17 '18 at 13:51

0

If your DBMS has a group_concat/listagg funtion it's simple

select distinct group_concat(value order by value)
from tab
group by id

answered Jun 17 '18 at 13:51

dnoeth

59,503
4
39
56

I should have noted that I am using T-SQL, which does not have such a function. Great answer. Sorry for my omission. – digest806 Jun 17 '18 at 14:00
@digest806: Well, there's STRING_AGG in SQLServer 2017 :-) Before you have to use FOR XML plus STUFF, e.g. https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – dnoeth Jun 17 '18 at 14:06

T-SQL (SQL Server): Extract unique sets/groups of data

2 Answers2