Nested JSON from database

Question

I have the following data in my database .

Date        Computer               Hits
==========  =====================  =======
2014-11-04  one                     1
2014-11-04  two                     249
2014-11-05  one                     200
2014-11-05  two                     50
2014-11-06  one                     75
2014-11-06  two                     100
2014-11-07  two                     50

Which would be generated randomly in the data base

i need to create a nested JSON

somewhat like

var computer = { "one" : [          { "Hits" : 1},

                                    { "Hits":200},

                                    { "Hits":75}
                                  ],                            
                  "two"       : [  { "Hits" : 1},

                                    { "Hits":200},

                                    { "Hits":75}
                                  ] // End "sales" Array.
                } 

I would need it in the following format as i am creating a stacked line series jqx chart , i tried using key value pairs and reading them directly from the data base but problem is it only gives a single line can anyone suggest a way

Answer 1

You could use "group by" and "stuff" in sql to get a group with a list of hits.

Then you can manipulate the values further in the code you use to call your sql to get the nested json.

select computer, 
 STUFF((SELECT '{Hits: ' + cast(value as varchar(10)) + '},'
       FROM @computerTable b 
       WHERE b.computer = a.computer
      FOR XML PATH('')), 1, 0, '')
 from @computerTable a
group by computer