0

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.

  • 1
    I suspect similar questions may have been asked (and possibly answered) before. Have you tried searching? – mustaccio Jun 3 '16 at 11:42
  • 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? – Max Vernon Jun 3 '16 at 11:50
  • 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. – Jon Moss Jun 3 '16 at 12:12
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.

| improve this answer | |
  • Thanks Les. This is exactly what I was after. Cheers :) – Jon Moss Jun 3 '16 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.