如何获取一行中的组名和组成员

Question

我有一个表，GroupMembers和GorupName在2列

Col 1 GroupMember       Col2 GroupName
       A1                        A
       A2                        A
       B1                        B
       B2                        B
       C1                        C
       C2                        C

如何获得输出结果

A - GroupName
A1 - GroupMember
A2 - GroupMember
B
B1
B2
C
C1
C2

这里我试图在单个列中获得GroupName和它的GroupMembers

Answer 1

;with Groups AS
(
    select distinct GroupName from YourTableName
)
,OrderedGroups AS
(
    select GroupName, ROW_NUMBER() Over(order by GroupName) R from Groups
)
,RankedData As
(
    select T.GroupMember, T.GroupName, OG.R from YourTableName T
    inner join OrderedGroups OG on T.GroupName = OG.GroupName
)
select GroupMember, R from RankedData
union
select GroupName, R from RankedData
order by R