Dense rank with separate attribute for order and partition-CodePudding

I gather data from two similar tables (with union all), this leads to this table :

create table t
   (id INT,
    pdc INT,
    typeBDC varchar(10)
   )

insert into t (id, pdc, typeBDC)
values
    (6945895, 11010, 'neg'),
    (6945896, 301010, 'neg'),
    (6449608, 100120, 'maro'),
    (6455861, 100120, 'maro'),
    (6879327, 211010, 'maro'),
    (6864349, 291010, 'maro'),
    (6917991, 291010, 'maro')

then i want to create a list of all id in the same typeBDC, ordered by pdc like this :

6945895                                  neg1
6449608,6455861,6879327,6864349,6917991  maro1
6945896                                  neg2

The order of the id inside a line is not important

To do this, i tried to use

select id, 
       pdc,
       typeBDC, 
       dense_rank() OVER (order by pdc) as tri
from t

but the [tri] attribute increments also when [pdc] changes

id       pdc   typeBDC  tri
6945895 11010   neg     1
6449608 100120  maro    2
6455861 100120  maro    2
6879327 211010  maro    3
6864349 291010  maro    4
6917991 291010  maro    4
6945896 301010  neg     5

which means that when i use coalesce i get more than one line per ordered typeBDC

CodePudding user response：

This is a gaps and island problem. Firstly you need to get the islands of data into groups, then you can get your DENSE_RANK, and then finally aggregate. I use a couple of nested CTEs to achieve this, firstly getting the groups, and then the DENSE_RANK:

With Grps AS(
    SELECT id,
           pdc,
           typeBDC,
           ROW_NUMBER() OVER (ORDER BY pdc) - 
           ROW_NUMBER() OVER (PARTITION BY typeBDC ORDER BY pdc) AS Grp
    FROM dbo.t),
Ranks AS(
    SELECT id,
           pdc,
           typeBDC,
           DENSE_RANK() OVER (PARTITION BY typeBDC ORDER BY Grp) AS Rnk
    FROM Grps G)
SELECT STRING_AGG(id,',') WITHIN GROUP (ORDER BY pdc),
       CONCAT(typeBDC,Rnk) AS typeBDC
FROM Ranks R
GROUP BY typeBDC,
         Rnk
ORDER BY MIN(pdc);

db<>fiddle