Create List Number

Question

My table looks 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 convert a table to a list as follows:

 +-------+--------+ | 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 tried rollup with row_number , but the result is still far from 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 helps

 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 1

Used by DENSE_RANK to get the desired numbering for the ID . Then CROSS APPLY to disable the data and mark which line is for Grp , Party or Member . Finally, use WHERE to filter only those rows that you need:

 WITH CteUnpivot AS( SELECT * FROM ( SELECT *, rnGrp = DENSE_RANK() OVER(ORDER BY Grp), rnParty = DENSE_RANK() OVER(PARTITION BY Grp ORDER BY Party), rnMember = ROW_NUMBER() OVER(PARTITION BY Grp, Party ORDER BY Member) FROM test ) t CROSS APPLY(VALUES ('Grp', Grp), ('Party', Party), ('Member', Member) ) x (col, [Text]) ) SELECT ID = CASE WHEN col = 'Grp' THEN CAST(rnGrp AS VARCHAR(3)) WHEN col = 'Party' THEN CAST(rnGrp AS VARCHAR(3)) + '.' + CAST(rnParty AS VARCHAR(3)) WHEN col = 'Member' THEN CAST(rnGrp AS VARCHAR(3)) + '.' + CAST(rnParty AS VARCHAR(3)) + '.' + CAST(rnMember AS VARCHAR(3)) END, [Text] FROM CteUnpivot WHERE (col = 'Grp' AND rnParty = 1 AND rnMember = 1) OR (col = 'Party' AND rnMember = 1) OR (col = 'Member') ORDER BY rnGrp, rnParty, rnMember; 

ONLINE DEMO

If the Member order does not matter, replace rnMember with:

 rnMember = ROW_NUMBER() OVER(PARTITION BY Grp, Party ORDER BY (SELECT NULL)) 

ONLINE DEMO

Answer 2

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 so that parents correctly generate hierarchy numbers. If you have duplicates in Member , then change ROW_NUMBER to DENSE_RANK inside CTE and add a different query from the last select

Note. If you use anything less than SQL SERVER 2012 , use + to concatenate instead of CONCAT

Answer 3

Honestly, I would not do this at the database level. Instead, I guarantee that the result is sorted by {Grp, Party, Member}, and then assigns "Id" values ​​in a single pass when displaying data.

However, if you decide to do this on the database server for any reason, you can use the dense_rank() function to get each individual identifier:

 ;with cte as ( select dense_rank() over (order by Grp) id0, dense_rank() over (partition by Grp order by Party) id1, dense_rank() over (partition by Grp, Party order by Member) id2, Grp, Party, Member from Table1 ), grps as (select distinct id0, Grp from cte), parties as (select distinct id0, id1, Party from cte), members as (select distinct id0, id1, id2, Member from cte), [list] as ( select cast(id0 as varchar(50)) as id, Grp as [Text] from grps union all select cast(id0 as varchar(50)) + '.' + cast(id1 as varchar(50)), Party from parties union all select cast(id0 as varchar(50)) + '.' + cast(id1 as varchar(50)) + '.' + cast(id2 as varchar(50)), Member from members ) select id, [Text] from [list] order by id 

Answer 4

This parameter does not use DENSE_RANK() , but ROW_NUMBER() , but is essentially similar to other published answers.

 With grps As ( Select Grp, GrpNo = Row_Number() Over (Order By Grp) From (Select Distinct Grp From MyTable) As MyTable), parties As ( Select MyTable.Grp, MyTable.Party, grps.GrpNo, PrtyNo = Row_Number() Over (Partition By MyTable.Grp Order By MyTable.Party) From (Select Distinct Grp, Party From MyTable) As MyTable Join grps On MyTable.Grp = grps.Grp), members As ( Select MyTable.Grp, MyTable.Party, MyTable.Member, parties.GrpNo, parties.PrtyNo, MbrNo = Row_Number() Over (Partition By MyTable.Grp, MyTable.Party Order By #groups.Member) From MyTable Join parties On MyTable.Grp = parties.Grp And MyTable.Party = parties.Party) Select ID = Convert(char(5), GrpNo), [Text] = Grp From grps Union All Select ID = Convert(char(1), GrpNo) + '.' + Convert(char(1), PrtyNo), [Text] = Party From parties Union All Select ID = Convert(char(1), GrpNo) + '.' + Convert(char(1), PrtyNo) + '.' + Convert(char(1), MbrNo), [Text] = Member From members;