Create pivot table from SQL query results

Question

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 :-

enter image description here

Desired results format :-

enter image description here

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.

• mustaccio: I suspect similar questions may have been asked (and possibly answered) before. Have you tried searching?
• Hannah Vernon: If there are 90,000 machines, how many users are there? If you pivot that it's going to be one damn huge result - what will you do with it?
• Jon Moss: There can be up to 10 users registered to each machine based on user device affinity rules. However, approx 82,000 machines only have 1 user as the Primary device user. The answer provided by Les below is exactly what I needed.

Answer 1

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.

• Jon Moss: Thanks Les. This is exactly what I was after. Cheers :)