title: Get concatenated value from result set
body: <p>I have this query:</p>
  
  <pre><code>ALTER procedure [dbo].[shuffleChoices]
      @subjectID int, @personID int, @itemCount int
  as
      SELECT TOP (@itemCount) question,
      answer = CONCAT('A. ',CA.[1]),
         distractor1 = CONCAT('B. ',CA.[2]),
         distractor2 = CONCAT('C. ',CA.[3]),
         distractor3 = CONCAT('D. ',CA.[4]),
         CONCAT(SUBSTRING(answer,0,2),trueanswer) AS trueanswer
  FROM   user_items
         CROSS APPLY (SELECT *
                      FROM   (SELECT x,
                                     ROW_NUMBER() OVER (ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
                              FROM   (VALUES(answer),
                                            (distractor1),
                                            (distractor2),
                                            (distractor3)) V(x)) ps 
                              PIVOT (MAX(x) FOR rn IN ([1], [2], [3], [4])) p) CA
  WHERE subjectID = @subjectID and personID = @personID
  ORDER BY NEWID()
  </code></pre>
  
  <p>What this does is it shuffles the values of columns <strong>answer</strong>, <strong>distractor1</strong>, <strong>distractor2</strong>, and <strong>distractor3</strong></p>
  
  <p>What I wanted to do was to get the letter of the correct answer. The values of columns <strong>answer</strong> and <strong>trueanswer</strong> are always the same without the shuffling inside the table. I tried to concatenate <strong>answer</strong> to <strong>trueanswer</strong> but the result I'm getting is this:</p>
  
  <p><a href="https://i.stack.imgur.com/BsHNF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BsHNF.png" alt="Result"></a></p>
  
  <p>It always get the first character of the value itself. How can I get the concatenated letters without updating the table? </p>