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

Question

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.

You may take a look at this link red-gate.com/simple-talk/sql/t-sql-programming/… — EricZ
– EricZ, Commented Sep 1, 2017 at 13:35
The earliest supported SQL Server version is 2012. JSon support was added in 2016. Why are you trying to generate JSON on the server, on a version that just doesn't know about it? Why not build the Json string on the client? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 1, 2017 at 13:36
You may also want to rethink the table's design. Attribute tables are actually an antipattern. You can't specify the correct types, you can't set constraings, can't index them. If you want the same table to store many differnt types of objects, just add extra columns. You can also use sparse columns to avoid wasting storage for unused fields. Or use XML fields — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 1, 2017 at 13:39
Finally, rethink the Json text itself. One would expect it to look like {Id:1, "Value1":"Data1", "Value2":"Data2"}. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 1, 2017 at 13:42

TT. · Accepted Answer · 2017-09-01 13:36:25Z

1

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

answered Sep 1, 2017 at 13:36

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TT. · Accepted Answer · 2017-09-01 13:23:34Z

0

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

edited Sep 1, 2017 at 13:23

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Sep 1, 2017 at 12:57

piyush jain

1038 bronze badges

1 Comment

cgsabari Over a year ago

Thank you @piyush for your reply. I don't want to change my compatibility level. I've updated the query which i've tried by using STUFF as my level. You can also check the same to try.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related