title: SQL Pivot Column that has multiple values for same column
body: <p>Trying to pivot table results that may have multiple rows with the same value
  I have data that looks like this so far.</p>
  
  <pre><code>Nbr      Person     Test
  
  33      Barry.      Prim
  33      Brian       Sup
  33      Burke RT    1st
  33      Ray         Add
  33      Jake        Add
  33      Smith       Add
  </code></pre>
  
  <p>I'm trying to pivot it so that it looks like this:</p>
  
  <pre><code>Nbr   Prim    Sup     1st       Add   Add2       Add3
  
  33   Barry   Brian   Burke      Ray   Jake       Smith
  </code></pre>
  
  <p>This is what I have so far with a normal pivot but it doesn't work to grab all the ones with the same value in the Test Column</p>
  
  <pre><code>CREATE TABLE #testTbl(nbr int,name varchar(20),test VARCHAR(10))
  INSERT INTO #testTbl
  SELECT '33','Barry','Prim'
  UNION
  SELECT '33','Brian','Sup'
  UNION
  SELECT '33','Burke','1st'
  UNION
  SELECT '33','Ray','Add'
  UNION
  SELECT '33','jake','Add'
  UNION
  SELECT '33','Smith','Add'
  
  
  select * from (
  Select * 
  from #testTbl
  ) as x
   pivot(
   max(name) for test in ([prim],[sup],[1st],[add])
  )
  as pivot1
  </code></pre>
  
  <p>Any help is greatly appreciated. If its not possible to have the columns output as Add Add2 and Add3 thats fine. Whatever works.</p>