title: Pivot TSQL table with dynamic columns
body: <p>I am passing up JSON as a parameter to a SQL stored proc.  I use a function that takes a JSON dictionary and creates a table with key value pairs (two columns) that I then use with a <code>COALESCE</code> to create dynamic sql for an <code>INSERT</code> statement.  This works fine for a single dictionary, but I need to also be able to send up JSON string that contains an array of dictionaries.  Right now, my parse function gives me this table variable:</p>
  
  <p><img src="https://i.stack.imgur.com/0cXlz.png" alt="enter image description here"></p>
  
  <p>What I need is a table variable like this:</p>
  
  <p><img src="https://i.stack.imgur.com/86o3X.png" alt="enter image description here"></p>
  
  <p>I can get the column names from the first table with this:</p>
  
  <pre><code>SELECT DISTINCT element name from @JSONTable
  </code></pre>
  
  <p>I should mention that these elementname's can and will change.  I will not know the number of distinct elementname values.  </p>
  
  <p>UPDATE - Using Umair's answer, I am getting close:</p>
  
  <pre><code>DECLARE @JSONString AS VARCHAR(MAX)
  
  SET @JSONString = '[{"ravid":3,"ravversion":2,"taskid":3},{"ravid":4,"ravversion":7,"taskid":99}]'
  
  IF OBJECT_ID('tempdb..#JSONTable') IS NOT NULL
      DROP TABLE #JSONTable
  
  
  CREATE TABLE #JSONTable
              (
                elementname VARCHAR(500) ,
                elementvalue VARCHAR(500)
              )
          INSERT  INTO #JSONTable
                  ( elementname ,
                    elementvalue
  
                  )
                  SELECT  NAME ,
                          StringValue
                  FROM    dbo.parseJSON(@JSONString)
                  WHERE   LEN(StringValue) &gt; 0 AND NAME IS NOT NULL
  
  
  
  --declare a csv variable with all the distinct elements
  DECLARE @csv NVARCHAR(max) = STUFF(
      (
           SELECT ',' + elementname
           FROM (
                SELECT DISTINCT elementname
                FROM #JSONTable
           ) AS e
           FOR XML PATH(''), TYPE
      ).value('.', 'nvarchar(max)'),
      1,
      1,
      ''
  );
  
  DECLARE @sql NVARCHAR(MAX) = '
      SELECT *
      FROM (
         SELECT *, Row = ROW_NUMBER() OVER (PARTITION BY elementname ORDER BY elementname)
         FROM #JSONTable
      ) AS t
      PIVOT (
          MAX(elementvalue)
          FOR elementname IN (' + @csv + ')
      ) AS p
  '
  
  EXEC sp_executesql @sql
  </code></pre>
  
  <p>But the dictionary values don't correspond to the key.  Here is the results of Umair's current answer:
  <img src="https://i.stack.imgur.com/FCTrL.png" alt="enter image description here"></p>