title: SQL query unknown rows into columns
body: <p>I asked this question and it was marked as a duplicate of <a href="https://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server/22773815#22773815">How to pivot unknown number of columns &amp; no aggregate in SQL Server?</a>, but that answer doesn't help me.</p>
  
  <p>I have a table of data that looks like this, with an unknown number of rows and values.</p>
  
  <pre><code>RecID     Name      Value
  1         Color     Red
  2         Size      Small
  3         Weight    20lbs
  4         Shape     Square
  </code></pre>
  
  <p>I need a query that will return the data like this, building out a column for each row.  I cannot hard code anything except the column headers 'Name' and 'Value'.</p>
  
  <pre><code>Color     Size     Weight     Shape
  Red       Small    20lbs      Square
  </code></pre>
  
  <p>Here is what I have so far that is partly working:</p>
  
  <pre><code>INSERT INTO @Table VALUES
  (1,'Color' ,'Red'),
  (2,'Size'  ,'Small'),
  (3,'Weight','20lbs'),
  (4,'Shape' ,'Square')
  
   ;with mycte
   as
   (
  SELECT rn,cols,val
  FROM   (SELECT  row_number() over(order by Name) rn, Name, Value
          FROM  @Table) AS src1
  UNPIVOT (val FOR cols
  IN ( [Name], [Value])) AS unpvt
   )
  
  SELECT *
  FROM   (SELECT rn,cols,val
          FROM   mycte) AS src2 PIVOT
  ( Max(val) FOR rn IN ([1], [2], [3])) AS pvt
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>cols    1   2   3
  Name    Color   Shape   Size
  Value   Red Square  Small
  </code></pre>
  
  <p>Two problems with this that I can't seem to resolve.  </p>
  
  <ol>
  <li>I don't need the column headers and the first column that has cols, Name, Value in it.</li>
  <li>Can't figure out how to have it build a column for each row without specifying the [x] identifiers.</li>
  </ol>
  
  <p>Any guidance would be great I've been stuck on this a while now.</p>