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
