How to select rows with multiple values i one column?-CodePudding

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

CodePudding user response：

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