开发者

How to get GroupName and GroupMembers in a Row

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

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜