I am using Microsoft sql server 2019. My table name is computer

I need a json with the following format

[
 {
 'Dell':[{"Model":"Vostro"},{"Model":"Precision"}]      
 },
 {
 'Lenovo':[{"Model":"Edge"},{"Model":"Horizon"}]
 }
]

I tried this SQL query

Select c1.make,(select Model from Computer c2 where c2.Make=c1.Make for json path) as model from Computer c1 group by c1.make for json path 

And my output is

"[{""make"":""Dell"",""model"":[{""Model"":""Vostro""},{""Model"":""Precision""}]},{""make"":""Lenovo"",""model"":[{""Model"":""Edge""},{""Model"":""Horizon""}]}]"

How to add column value as key. Change model key to Dell, Lenovo

CodePudding user response：

Please find the below script,

;WITH CTE AS (                           
SELECT  
    CONCAT('{','''',Make,'''',':',(SELECT Model FROM #T WHERE Make = T.Make FOR JSON AUTO),'}' ) JSONData
FROM (
SELECT DISTINCT Make
FROM #T  ) T  )


SELECT '[' STRING_AGG(JsonData,',') ']'
FROM CTE

Since the column Naming is dynamic we will not be able to static query for the expected output. So we can STUFF or STRING CONCAT to attain the Output.