title: MSSQL dynamic pivot column values to column header
body: <p>Could someone help me convert the following on mssql please?</p>
  
  <pre><code> ID   |  PROPERTY_NAME  | PROPERTY_VALUE
   1    |      name1      |     value
   1    |      name2      |     value
   1    |      name3      |     value
   2    |      name4      |     value
   2    |      name2      |     value
   3    |      name6      |     value
  ..
  </code></pre>
  
  <p>PROPERTY_NAME  &amp; PROPERTY_VALUE  being the headers, id having multiple 'properties'</p>
  
  <p>I want to convert it to:</p>
  
  <pre><code>ID  |  NAME1  | NAME2  | NAME3  | NAME4  | NAME5  | NAME6  | nameETC...
  1   |  value  | value  | value  |        |        |        | valueETC...
  2   |         | value  |        | value  |        |        | valueETC...
  3   |         |        |        |        |        | value  | valueETC...
  ..
  </code></pre>
  
  <p>Where NAME1 | NAME2 | NAME3 etc are now the column headers.</p>
  
  <p>I'm guessing a pivot and a 'select distinct PROPERTY_NAME from MycoolTable' but can't seem to put the two together.</p>
  
  <p>This is as far as i got: (no server_id) column and all nulls everywhere) Clearly i'm stupid :D</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
  
  select @cols = STUFF((SELECT distinct 
             ',' + QUOTENAME(PROPERTY_NAME)
                 FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                 ,1,1,'');
  
  SET @query = 'SELECT  '+ @cols + ' from 
           (
              SELECT SERVER_ID, PROPERTY_NAME, PROPERTY_CHAR_VAL
              FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
          ) x
          pivot 
          (
              MAX(SERVER_ID)
              for PROPERTY_CHAR_VAL in (' + @cols + ')
          ) p ';
  
  execute(@query)
  </code></pre>
  
  <p>Many thanks,
  Mike</p>