开发者

How to get GroupName and GroupMembers in a Row

开发者 https://www.devze.com 2023-03-20 16:48 出处:网络
I have a table with GroupMembers and GorupName in 2 Columns as Col 1 GroupMemberCol2 GroupName A1A A2A B1开发者_运维百科B

I have a table with GroupMembers and GorupName in 2 Columns as

Col 1 GroupMember       Col2 GroupName

       A1                        A
       A2                        A
       B1                开发者_运维百科        B
       B2                        B
       C1                        C
       C2                        C

How to get output result as

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

Here I am trying to get the GroupName and its GroupMembers in a single Column


;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
0

精彩评论

暂无评论...
验证码 换一张
取 消