How to select rows with multiple values i one column?

Question

SQL Server. I have a table and want to select rows which have the same value in one column?

Example:

declare @t table (id int not null, parent_id int not null, name nvarchar(128))

insert into @t(id, parent_id, name) values(1, 0, N'1-1')
insert into @t(id, parent_id, name) values(10, 1, N'10-1')
insert into @t(id, parent_id, name) values(11, 1, N'11-1')
insert into @t(id, parent_id, name) values(12, 1, N'12-2')
insert into @t(id, parent_id, name) values(21, 10, N'21-11')
insert into @t(id, parent_id, name) values(31, 12, N'31-12')
insert into @t(id, parent_id, name) values(32, 13, N'32-12')
insert into @t(id, parent_id, name) values(33, 13, N'33-12')
insert into @t(id, parent_id, name) values(34, 13, N'34-12')

The expected result is:

id={10, 11, 12} and id ={32,33,34}

because they have the same parent_id

The combination of GROUP BY and HAVING is a possible option, but what is your current attempt and what is the expected output format? — Zhorov
– Zhorov, Commented Oct 10, 2022 at 12:55
I have tried select id, count(parent_id) from @t group by id, parent_id having count(parent_id)>1 but it is not correct — ZedZip
– ZedZip, Commented Oct 10, 2022 at 12:57

John Cappelletti · Accepted Answer · 2022-10-10 13:15:39Z

3

string_agg() would be an option

Select parent_id
      ,Array = string_agg(id,',')
 From  @t
 Group By parent_id
 having count(*)>1

Results

parent_id   Array
1           10,11,12
13          32,33,34

As rows

 Select  top 1 with ties *
  from  @T
  order by case when sum(1) over (partition by parent_id) >1 then 0 else 1 end


id  parent_id   name
10  1           10-1
11  1           11-1
12  1           12-2
32  13          32-12
33  13          33-12
34  13          34-12

edited Oct 10, 2022 at 13:15

answered Oct 10, 2022 at 12:59

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ZedZip Over a year ago

Great, thank you, and how to select id-s ? I mean not agg to string but as rows

Gabriele Petrioli Over a year ago

SELECT * FROM @t WHERE parent_id in (SELECT parent_id FROM @t GROUP BY parent_id HAVING COUNT(id) > 1)

John Cappelletti Over a year ago

@ZedZip Gabriele's option or as the updated answer

Collectives™ on Stack Overflow

How to select rows with multiple values i one column?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related