SQL Query : User Device Affinity Report

The first query writes to a temporary dataset then the second query is used to create a pivot table.  The only limitation is you have to specify the number of ‘user’ columns you want to display per machine.  The following example displays 5 users per device.  The Sample Output is obscured for client confidentiality.

SELECT 
UMS.MachineResourceName,
UMS.UniqueUserName
INTO #TempTable
FROM v_UserMachineRelationship UMS
join v_UserMachineRelation UMR on UMS.RelationshipResourceID = UMR.RelationshipResourceID
join v_R_System VRS on UMR.MachineResourceID = VRS.ResourceID
WHERE VRS.Operating_System_Name_and0 like '%Workstation%'


SELECT *
FROM
(SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [MachineResourceName] ORDER BY [MachineResourceName])) [User]
,*
FROM #TempTable) Src
PIVOT
(MAX(UniqueUsername) FOR [User] IN ([User1], [User2],[User3],[User4],[User5])
)Pvt

Sample Output