SQL JSON Ouptput

Question

SQL JSON Ouptput

sks_989

SSC Eights!

Points: 843
More actions
August 18, 2021 at 1:50 pm

#3921277

I have table with code type , code & Value. I want out in json like this way.
In this example Code type test 1 . I try to do union which not allowing and i dont want to put code type instead i want my own name.

{
{ "Test1" [
{ "code" : "1",
"Value" : "2"
},
{ "code" : "2",
"Value" : "3"
}],
"Test2" [
{ "code" : "4",
"Value" : "5"
},
{ "code" : "6",
"Value" : "7"
}]
}

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

declare @t table(codetype varchar(10), code int, Value int);
insert into @t(codetype, code, Value)
values('Test1',1,2),
('Test1',2,3),
('Test2',4,5),
('Test2',6,7);


SELECT 
   N'{' + 
   STUFF(
      (
      SELECT DISTINCT N',"' +  t.codetype + '":' +  ca.jsn
      FROM @t AS t
      CROSS APPLY (SELECT t2.code, t2.Value FROM @t t2 WHERE t2.codetype = t.codetype FOR JSON PATH ) ca(jsn)
      FOR XML PATH('')
      ), 1, 1, N''
   ) + N'}'
 AS Result;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

sks_989 SSC Eights! Points: 843 More actions · Answer 2

Thanks Mark. Really appreciate for fast response. Really help me.