sql - 生成 list 编号

Question

我有一个如下所示的表格:

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

我想将表格转换为这样的列表:

+-------+--------+
|  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  |
+-------+--------+

我已经尝试使用row_number进行rollup，但结果仍然与我想要的相距甚远

;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

提前致谢。

编辑
这是生成表的SQL，希望对你有帮助

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

这使用DENSE_RANK来获取ID的正确编号。然后CROSS APPLY to unpivot数据并标记哪一行属于 Grp、Party 或 Member。最后使用 WHERE 只过滤您需要的行:

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

如果 Member 的顺序不重要，请将 rnMember 替换为:

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

ONLINE DEMO