CODEWITHSUNDEEP
sql-serversql-server-2008-r2
cgsabari
cgsabari

Reputation: 615

T-SQL select query with generate JSON result group by one column in SQL server 2008

Here I've a case to get values into JSON group by a specific cloumn from a table in SQL Server 2008 R2.

Consider TableName is Test,

---------------------------
Id     Value       Data
---------------------------
1      Value1      Data1
2      Value2      Data2
2      Value3      Data3
2      Value4      Data4
3      Value5      Data5
3      Value6      Data6
---------------------------

My output should be as below,

------------------------------------------------------------------------------
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"}]
------------------------------------------------------------------------------

EDIT: I've tried below query to get the output, but it doesn't work.

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

Output of above query is,

----------------------------------------
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"}]

But, I can't achieve my expected output. Anybody suggest me to achieve this output.

Upvotes: 1

Views: 2119

Answers (2)

TT.
TT.

Reputation: 16146

You were close. This is a sample script that gives you desired output.

DECLARE @tt TABLE(Id INT, Value NVARCHAR(256), Data NVARCHAR(256));
INSERT INTO @tt(Id,Value,Data)
VALUES
    (1,N'Value1',N'Data1'),
    (2,N'Value2',N'Data2'),
    (2,N'Value3',N'Data3'),
    (2,N'Value4',N'Data4'),
    (3,N'Value5',N'Data5'),
    (3,N'Value6',N'Data6');

SELECT
    t_o.Id,
    ValueDatas='['+STUFF((
        SELECT
            ', {"Value" : "'+ Value +'", "Data" : "' + Data + '"}'
        FROM
            @tt AS t_i
        WHERE
            t_i.Id=t_o.Id
        FOR XML
            PATH(''), TYPE
    ).value('.[1]','NVARCHAR(MAX)'),1,2,'')+']'
FROM
    @tt AS t_o
GROUP BY
    t_o.Id

Upvotes: 1

piyush jain
piyush jain

Reputation: 113

Unfortunately, JSON feature is not supported in SQL Server 2008, It may be possibility of some workaround. However, You may use below query in SQL Server 2016 with compatibility level 130 

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130

SELECT Id,Value,Data FROM
FOR JSON AUTO

For more info, please refer below article - https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

Upvotes: 0

Related Questions