need to get data grouped by groupid

Question

I want to group the query data based on the groupid attribute, the following image is my table image

http://prntscr.com/g2qu7a

I want it to show it like a tree structure where i can show the data in this format:

Group1
    price
     length
      width
    price
     length
      width
    price
     length
      width

Group 2
   same as above 

the 3 elements under each group can have 3 or less than 3 or just one or nothing, i want to get data so i can create textfields to update dat in my database, but that is not what i am trying

here is my query

select priceid,itemid,groupid,price,length,width from prices 

Answer 1

Try using the following concept:

Insert data:

CREATE TABLE some_table (some_data VARCHAR(20), some_other_data VARCHAR(20), groupId VARCHAR(20));
INSERT INTO some_table (some_data, some_other_data, groupId) values ('a', '1', 'id1');
INSERT INTO some_table (some_data, some_other_data, groupId) values ('b', '2', 'id1');
INSERT INTO some_table (some_data, some_other_data, groupId) values ('c', '3', 'id2');

Execute the query:

SELECT '{"' + t.groupId + '": [{' + STUFF(
          (
            SELECT '{ "some_data":"' + td.some_data + '"', ', "some_other_data":' + td.some_other_data + '},'
            FROM some_table td
            WHERE t.groupId = td.groupId
            FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ']}'
FROM some_table t
GROUP BY t.groupId

Results:

{"id1": [{ "some_data":"a", "some_other_data":1},{ "some_data":"b","some_other_data":2},]}

{"id2": [{ "some_data":"c","some_other_data":3},]}

Note the trailing comma should not affect the validity of the data. My experience with SQL Server is limited, but this should get you started in a direction that can work.

Fiddle:

http://sqlfiddle.com/#!6/66b19/35