By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
with tbltmp(x, y) as (
select * from (
VALUES
(5215, 57),
(18943, 221),
(18943, 230),
(18943, 238),
(21488, 257),
(21488, 270),
(5215, 67),
(5215, 77),
(5215, 87),
(5215, 97)
) as t(x, y)
),
rtbltmp as (
select d.*,
dense_rank() over(order by x) as rnx,
row_number() over(partition by x order by y) as rny,
count(y) over(partition by x) as cnt
from tbltmp d
)
, solution(x,y, rnx, rny, js) as (
select x,y, rnx, rny, cast(concat('[' , x , ',' , y , ']') as varchar(max))
from rtbltmp
where rnx = 1
union all
select d.x, d.y, d.rnx, d.rny,
concat(r.js, ',[', d.x, ',', d.y, ']')
from solution r
join rtbltmp d
on
(d.rnx = r.rnx + 1)
),
| grp | js |
|---|---|
| 1 | [[5215,97],[18943,238],[21488,257]] |
| 2 | [[5215,97],[18943,238],[21488,270]] |
| 3 | [[5215,97],[18943,230],[21488,257]] |
| 4 | [[5215,97],[18943,230],[21488,270]] |
| 5 | [[5215,97],[18943,221],[21488,257]] |
| 6 | [[5215,97],[18943,221],[21488,270]] |
| 7 | [[5215,87],[18943,238],[21488,257]] |
| 8 | [[5215,87],[18943,238],[21488,270]] |
| 9 | [[5215,87],[18943,230],[21488,257]] |
| 10 | [[5215,87],[18943,230],[21488,270]] |
| 11 | [[5215,87],[18943,221],[21488,257]] |
| 12 | [[5215,87],[18943,221],[21488,270]] |
| 13 | [[5215,77],[18943,238],[21488,257]] |
| 14 | [[5215,77],[18943,238],[21488,270]] |
| 15 | [[5215,77],[18943,230],[21488,257]] |
| 16 | [[5215,77],[18943,230],[21488,270]] |
| 17 | [[5215,77],[18943,221],[21488,257]] |
| 18 | [[5215,77],[18943,221],[21488,270]] |
| 19 | [[5215,67],[18943,238],[21488,257]] |
| 20 | [[5215,67],[18943,238],[21488,270]] |
| 21 | [[5215,67],[18943,230],[21488,257]] |
| 22 | [[5215,67],[18943,230],[21488,270]] |
| 23 | [[5215,67],[18943,221],[21488,257]] |
| 24 | [[5215,67],[18943,221],[21488,270]] |
| 25 | [[5215,57],[18943,238],[21488,257]] |
| 26 | [[5215,57],[18943,238],[21488,270]] |
| 27 | [[5215,57],[18943,230],[21488,257]] |
| 28 | [[5215,57],[18943,230],[21488,270]] |
| 29 | [[5215,57],[18943,221],[21488,257]] |
| 30 | [[5215,57],[18943,221],[21488,270]] |