TSQL Issues Team
I have a TSQL query that I am trying to group data with. The table contains the user records and the access keys they hold, such as site admin, moderator, etc. The PC is on the user and access button because the user can exist multiple times with different keys.
Now I am trying to display a table of all users and in one column, all the keys the user is holding.
If bob had three separate entries for its three separate access keys, the result should only have one entry for bob with all three access levels.
SELECT A.[FirstName],
A.[LastName],
A.[ntid],
A.[qid],
C.FirstName AS addedFirstName,
C.LastName AS addedLastName,
C.NTID AS addedNTID,
CONVERT(VARCHAR(100), p.TIMESTAMP, 101) AS timestamp,
(
SELECT k.accessKey,
k.keyDescription
FROM TFS_AdhocKeys AS k
WHERE p.accessKey = k.accessKey
FOR XML PATH ('key'), TYPE, ELEMENTS, ROOT ('keys')
)
FROM TFS_AdhocPermissions AS p
LEFT OUTER JOIN dbo.EmployeeTable as A
ON p.QID = A.QID
LEFT OUTER JOIN dbo.EmployeeTable AS C
ON p.addedBy = C.QID
GROUP BY a.qid
FOR XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');
END
I am trying to group the data by a.qid, but this forces me to group in each column in the select element, which will then not be unique, so it will contain duplicates.
What else is needed for this?
Currently
UserID | accessKey
123 | admin
123 | moderator
Desired:
UserID | accessKey
123 | admin
moderator
source to share
I was working on something recently and had a similar problem. Like your request, I had an inner "for xml" with joins in the outer "for xml". It turned out that it works better if the connections were in the internal "for xml". The code will be inserted below. Hope this helps.
Select
(Select Institution.Name, Institution.Id
, (Select Course.Courses_Id, Course.Expires, Course.Name
From
(Select Course.Courses_Id, Course.Expires, Courses.Name
From Institutions Course Course Join Courses On Course.Courses_Id = Courses.Id
Where Course.Institutions_Id = 31) As Course
For Xml Auto, Type, Elements) As Courses
From Institutions Institution
For Xml Auto, Elements, Root('Institutions') )
source to share
Since I have no definitions for the other tables I have, I am just doing test data and you can follow this to answer your question.
Create operator
CREATE TABLE #test(UserId INT, AccessLevel VARCHAR(20))
Insert sample data
INSERT INTO #test VALUES(123, 'admin')
,(123, 'moderator')
,(123, 'registered')
,(124, 'moderator')
,(124, 'registered')
,(125, 'admin')
Using ROW_NUMBER()
you can achieve what you need
;WITH C AS(
SELECT ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY UserId) As Rn
,UserId
,AccessLevel
FROM #test
)
SELECT CASE Rn
WHEN 1 THEN UserId
ELSE NULL
END AS UserId
,AccessLevel
FROM C
Output
UserId AccessLevel
------ -----------
123 admin
NULL moderator
NULL registered
124 moderator
NULL registered
125 admin
source to share