Alex Alex - 23 days ago 7
SQL Question

Generate list number

I have a table look like this:

+-------+--------+--------+
| Grp | Party | Member |
+-------+--------+--------+
| FC | Party1 | Tom |
| FC | Party1 | Alice |
| FC | Party2 | John |
| FC | Party3 | Mary |
| GC | Party2 | Anna |
| GC | Party4 | Alex |
| GC | Party5 | Diana |
+-------+--------+--------+


I want to transform the table into list like this:

+-------+--------+
| ID | Text |
+-------+--------+
| 1 | FC |
| 1.1 | Party1 |
| 1.1.1 | Tom |
| 1.1.2 | Alice |
| 1.2 | Party2 |
| 1.2.1 | John |
| 1.3 | Party3 |
| 1.3.1 | Mary |
| 2 | GC |
| 2.1 | Party2 |
| 2.1.1 | Anna |
| 2.2 | Party4 |
| 2.2.1 | Alex |
| 2.3 | Party5 |
| 2.3.1 | Diana |
+-------+--------+


I have tried
rollup
with
row_number
, but the result still far away what I want

;with ctx as (
select * from @test
group by rollup(Grp, Party, Member)
)
select row_number() over (partition by grp order by grp, party, member) as g,
row_number() over (partition by grp, party order by grp, party, member) as p,
row_number() over (partition by grp, party, member order by grp, party, member) as m,
grp, party, member
from ctx
where grp is not null
order by grp, party, member


Thanks in advance.

EDIT

Here is the SQL to generate the table, hope this can help

declare @test table (Grp varchar(10), Party varchar(10), Member varchar(20))

insert into @test values ('FC', 'Party1', 'Tom')
insert into @test values ('FC', 'Party1', 'Alice')
insert into @test values ('FC', 'Party2', 'John')
insert into @test values ('FC', 'Party3', 'Mary')
insert into @test values ('GC', 'Party2', 'Anna')
insert into @test values ('GC', 'Party4', 'Alex')
insert into @test values ('GC', 'Party5', 'Diana')

Answer

Here is one way

;WITH cte
     AS (SELECT Dense_rank()OVER (ORDER BY grp)    AS g,
                Dense_rank()OVER (partition BY grp ORDER BY party)  AS p,
                Row_number()OVER (partition BY grp, party ORDER BY member) AS m,
                grp,
                party,
                member
         FROM   @test
         WHERE  grp IS NOT NULL) 
SELECT DISTINCT grp,
                Cast(g AS VARCHAR(10)) AS [Text]
FROM   cte
UNION ALL
SELECT DISTINCT party,
                Concat(g, '.', p)
FROM   cte
UNION ALL
SELECT member,
        Concat(g, '.', p, '.', m)
FROM   cte
ORDER  BY [Text] 

You need to use DENSE_RANK for parents to generate hierarchy numbers properly. If you have duplicates in Member as well then change the ROW_NUMBER to DENSE_RANK inside CTE and add distinct to the final select query

Note : If you are using anything less than SQL SERVER 2012 then use + operator for concatenation instead of CONCAT