Create pivot table from SQL query results I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name.
Example of query results :-



Desired results format :-

 

Is it possible to create the desired table as part of the query, or do I need to manipulate the results as a second step?  There are 130,000 lines in my original query output representing approx 90,000 machines.

Hope somebody can help.
Here you go. This is for SQL Server (since you haven't tagged the DB) but I guess this is OK since you mention SCCM.

There are heaps of examples of dynamic pivots on here if the final number of [UserX] columns is unknown.

SELECT * INTO #Temp FROM (VALUES 
 ('PC001', 'a1\user1')
,('PC002', 'a1\user2')
,('PC002', 'a1\user3')
,('PC003', 'a1\user4')
,('PC003', 'a1\user5')
,('PC003', 'a1\user6')
,('PC004', 'a1\user7')
,('PC005', 'a1\user8')
) A([Computer Name], UserName);

SELECT *
 FROM
(SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
FROM #Temp) Src
PIVOT
(MAX(Username) FOR [User] IN ([User1], [User2],[User3])
)Pvt


Computer Name User1    User2    User3
------------- -------- -------- --------
PC001         a1\user1 NULL     NULL
PC002         a1\user2 a1\user3 NULL
PC003         a1\user4 a1\user5 a1\user6
PC004         a1\user7 NULL     NULL
PC005         a1\user8 NULL     NULL

(5 row(s) affected)


Off the top of my head, yes, you can add this to your original query - put your query in place of my #Temp as in

SELECT *
   FROM
  (SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
  FROM (Your query goes here) Blah
) Src
etc


) or just select your query into a temp table - just add INTO #TempTableName into your query.

Hope this helps.