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
精彩评论