How can I obtain the below output in sql server 2012.
Table
ID | Values|
1 a
1 b
1 c
2 d
2 e
The output should be such that the first row has a fixed number of values(2) seperated by comma and the next row has the remaining values seperated by comma
ID
ID | Values|
1 a,b
1 c
2 d,e
Each id should contain maximum two values in a single row.The remaining values should come in the next row.
Try to use my code:
use db_test;
create table dbo.test567
(
id int,
[values] varchar(max)
);
insert into dbo.test567
values
(1, 'a'),
(1, 'b'),
(1, 'c'),
(2, 'd'),
(2, 'e')
with cte as (
select
id,
[values],
row_number() over(partition by id order by [values] asc) % 2 as rn1,
(row_number() over(partition by id order by [values] asc) - 1) / 2 as rn2
from dbo.test567
), cte2 as (
select
id, max(case when rn1 = 1 then [values] end) as t1, max(case when rn1 = 0 then [values] end) as t2
from cte
group by id, rn2
)
select
id,
case
when t2 is not null then concat(t1, ',', t2)
else t1
end as [values]
from cte2
order by id, [values]
