title: T-SQL select query with generate JSON result group by one column in SQL server 2008
body: <p>Here I've a case to get values into JSON group by a specific cloumn from a table in SQL Server 2008 R2.</p>
  
  <p>Consider TableName is <strong>Test</strong>,</p>
  
  <pre><code>---------------------------
  Id     Value       Data
  ---------------------------
  1      Value1      Data1
  2      Value2      Data2
  2      Value3      Data3
  2      Value4      Data4
  3      Value5      Data5
  3      Value6      Data6
  ---------------------------
  </code></pre>
  
  <p>My output should be as below,</p>
  
  <pre><code>------------------------------------------------------------------------------
  Id    ValueDatas
  ------------------------------------------------------------------------------
  1     [{"Value" : "Value1", "Data" : "Data1"}]
  2     [{"Value" : "Value2", "Data" : "Data2"}, {"Value" : "Value3", "Data" : 
        "Data3"}, {"Value" : "Value4", "Data" : "Data4"}]
  3     [{"Value" : "Value5", "Data" : "Data5"}, {"Value" : "Value6", "Data" : 
        "Data6"}]
  ------------------------------------------------------------------------------
  </code></pre>
  
  <p><strong>EDIT:</strong>
      I've tried below query to get the output, but it doesn't work.</p>
  
  <pre><code>SELECT Id, '[' +  STUFF((SELECT ',{"Value":"' + Value +'"'+
     + ',"Data":"' + Data + '"}'
  FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') + ']' AS ValueDatas
  FROM @TABLE
  Group by Id, Value, Data
  </code></pre>
  
  <p>Output of above query is,</p>
  
  <pre><code>----------------------------------------
  Id  ValueDatas
  ----------------------------------------
  1   [{"Value":"Value1","Data":"Data1"}]
  2   [{"Value":"Value2","Data":"Data2"}]
  2   [{"Value":"Value3","Data":"Data3"}]
  2   [{"Value":"Value4","Data":"Data4"}]
  3   [{"Value":"Value5","Data":"Data5"}]
  3   [{"Value":"Value6","Data":"Data6"}]
  </code></pre>
  
  <p>But, I can't achieve my expected output. Anybody suggest me to achieve this output.</p>