CODEWITHSUNDEEP
sql-serverjson
Subin
Subin

Reputation: 89

Sql to json in particular format

I have a datatable in the following structure.

FlatName    Occupied Vacant
A           2          2
B           0          4
C           0          4
D           0          4
E           0          4
F           0          4
G           0          4
H           0          4
I           0          4
J           0          4

I would like to serialize it as a JSON object where the FlatName is parent and Occupied & Vacant are the child in a array like

 {
                        series: [{
                            name: 'A',
                            data: [2,2]
                                }, {
                            name: 'B',
                            data: [0,4]
                                }, {
                            name: 'V',
                            data: [0,4]
                                }]
    ..........
 }

Please help me to solve it.

Upvotes: 2

Views: 58

Answers (1)

Garry
Garry

Reputation: 4533

There is a way described here: http://www.codeproject.com/Articles/815371/Data-Parsing-SQL-to-JSON

But here you have to go through multiple steps like convert your SQL to XML and then to JSON. It is not in the format you are expecting but it shows the way.

There will be build in support for JSON in SQL Server 2016 (public preview is available blog link)

Exporting data as JSON - FOR JSON First feature that will be available in SQL Server 2016 CTP2 is ability to format query results as JSON text using FOR JSON clause. If you are familiar with FOR XML clause you will easily understand FOR JSON:

SELECT column, expression, column as alias
 FROM table1, table2, table3
 FOR JSON [AUTO | PATH]

Upvotes: 1

Related Questions