How to assign a username to a random userid?

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  February 16, 2017 at 7:58 am

  #327293

  I have a table that collect customer responses, see code below:

  if (OBJECT_ID('tempdb..#RankUsers') is not null) Drop Table #RankUsers
Create Table #RankUsers (UserID int, FormID int, Answers varchar (50))
Insert Into #RankUsers
 Values
 (101, 2001, 'Yes'),
 (102, 2001, 'No'),
 (102, 2002, 'Maybe'),
 (103, 2002, 'Not Sure'),
 (101, 2003, 'Ok'),
 (102, 2004, 'No'),
 (105, 2004, 'Maybe')
  
  By rule, we're going to retrieve up to two answers per survey form. My task is to display the results in one row per survey form. Here my code using pivot function:
  Select * From 
 (Select UserID, FormID, Answers
  From #RankUsers ru
  ) src
  Pivot
  (
  Max(Answers)
  for userID in ([101], [102], [103], [104], [105])
  ) piv;

  Since there are only maxium of two users' answers per survey form, the management ask if it is possible to randomly assign UserA to the first userid, and UserB to the second, if exists. So the final report will be looked like this:
  FormID UserA UserB
  2001 Yes No
  2002 Maybe Not Sure
  2003 Ok Null
  2004 No Maybe

  Please help.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 16, 2017 at 9:25 am

  #1928886

  Try this

  WITH CTE AS (
  SELECT UserID, FormID, Answers,
     ROW_NUMBER() OVER(PARTITION BY FormID ORDER BY NEWID()) AS rn
  FROM #RankUsers)
  SELECT FormID,
     MAX(CASE WHEN rn = 1 THEN Answers END) AS UserA,
     MAX(CASE WHEN rn = 2 THEN Answers END) AS UserB
  FROM CTE
  GROUP BY FormID
  ORDER BY FormID;

  Oops, not what you want

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  February 16, 2017 at 9:33 am

  #1928888

  This is exactly what I needed, thank you very much!

  I run it with your code, and here is the result:
  FormID UserA UserB
  2001 Yes No
  2002 Maybe Not Sure
  2003 Ok NULL
  2004 Maybe No

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  February 20, 2017 at 9:54 am

  #1929389

  Mark Cowne - Thursday, February 16, 2017 9:25 AM

  Try this

  WITH CTE AS (
  SELECT UserID, FormID, Answers,
     ROW_NUMBER() OVER(PARTITION BY FormID ORDER BY NEWID()) AS rn
  FROM #RankUsers)
  SELECT FormID,
     MAX(CASE WHEN rn = 1 THEN Answers END) AS UserA,
     MAX(CASE WHEN rn = 2 THEN Answers END) AS UserB
  FROM CTE
  GROUP BY FormID
  ORDER BY FormID;

  Oops, not what you want

  Hi Mark,  Can you explain what you meant by saying "not what you want"?  I tested it briefly and everything seemed to be working fine.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 20, 2017 at 1:43 pm

  #1929437

  jay-125866 - Monday, February 20, 2017 9:54 AM

  Mark Cowne - Thursday, February 16, 2017 9:25 AM

  Try this

  WITH CTE AS (
  SELECT UserID, FormID, Answers,
     ROW_NUMBER() OVER(PARTITION BY FormID ORDER BY NEWID()) AS rn
  FROM #RankUsers)
  SELECT FormID,
     MAX(CASE WHEN rn = 1 THEN Answers END) AS UserA,
     MAX(CASE WHEN rn = 2 THEN Answers END) AS UserB
  FROM CTE
  GROUP BY FormID
  ORDER BY FormID;

  Oops, not what you want

  Hi Mark,  Can you explain what you meant by saying "not what you want"?  I tested it briefly and everything seemed to be working fine.

  After I posted my solution, I reread your original post

  'randomly assign UserA to the first userid, and UserB to the second,'

  From this I thought you were expecting *all* answers for a particular user to be *one* column so
  you would end up with something like this where, for example, UserA is 102 and UserB is 105

  FormID UserA  UserB
  2001    No       NULL
  2002   Maybe   NULL
  2003   NULL    NULL
  2004    No       Maybe

  This isn't a difficult change, but as it turns out my solution worked correctly for you anyway.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  February 21, 2017 at 9:58 am

  #1929561

  Mark Cowne - Monday, February 20, 2017 1:42 PM

  jay-125866 - Monday, February 20, 2017 9:54 AM

  Mark Cowne - Thursday, February 16, 2017 9:25 AM

  Try this

  WITH CTE AS (
  SELECT UserID, FormID, Answers,
     ROW_NUMBER() OVER(PARTITION BY FormID ORDER BY NEWID()) AS rn
  FROM #RankUsers)
  SELECT FormID,
     MAX(CASE WHEN rn = 1 THEN Answers END) AS UserA,
     MAX(CASE WHEN rn = 2 THEN Answers END) AS UserB
  FROM CTE
  GROUP BY FormID
  ORDER BY FormID;

  Oops, not what you want

  Hi Mark,  Can you explain what you meant by saying "not what you want"?  I tested it briefly and everything seemed to be working fine.

  After I posted my solution, I reread your original post

  'randomly assign UserA to the first userid, and UserB to the second,'

  From this I thought you were expecting *all* answers for a particular user to be *one* column so
  you would end up with something like this where, for example, UserA is 102 and UserB is 105

  FormID UserA  UserB
  2001    No       NULL
  2002   Maybe   NULL
  2003   NULL    NULL
  2004    No       Maybe

  This isn't a difficult change, but as it turns out my solution worked correctly for you anyway.

  Thank you very much, Mark.