Sql Data as tree structure to bind a asp.net grid

Question

I have a sql query like

SELECT   
       A.Id
      ,C.[inv_number]
      ,C.[inv_date]
      ,C.[com_id]
      ,C.[com_name]
      ,C.[total_amount] as AmountDr
      ,sum(A.total_amount) as AmountCr
      ,B.category
  FROM invoice_break_up A 
  left join fee_category B on A.fee_id= B.fee_id 
  left join invoice_master C on A .invoice_id=C.invoice_id
  where A.total_amount>0
  group by A.invoice_id,A.fee_id,B.category,C.[inv_number]
      ,C.[inv_date]
      ,C.[com_id]
      ,C.[com_name]
      ,C.[total_amount]
      ,C.[created_date]

i am getting the result as expected like

But i need to get the result as

The result i need to bind to a asp.net gridview. Is there any way for this. Please help.

Answer 1

Use the following to get the required output:

Table name: Table_Group

GroupId - Name

1 - Food

2 - TV

Table name: Table_Product

ProductId - GroupId - ProductName

1 - 1 - Pepsi

2 - 1 - 7up

3 - 1 - Fanta

4 - 2 - Sony

5 - 2 - LG

Table name: Table_Location

LocationId - GroupId - LocationName

1 - 1 - Bangladesh

2 - 2 - China

Finally run the following script: Modified

SELECT
CASE WHEN t.ProductId = (SELECT TOP 1 ProductId -- Sub query
FROM Table_Product t3
WHERE t3.GroupId = t.GroupId
ORDER BY t3.GroupId) THEN q.LocationName
ELSE ''
END AS LocationName,

CASE WHEN t.ProductId = (SELECT TOP 1 t2.ProductId -- Sub query
FROM Table_Product t2
WHERE t2.GroupId = p.GroupId
ORDER BY t2.GroupId) THEN p.Name
ELSE ''
END AS Category, 

t.ProductName
FROM Table_Product t
LEFT JOIN Table_Group p ON p.GroupId = t.GroupId
LEFT JOIN Table_Location q ON q.GroupId = t.GroupId
ORDER BY t.GroupId

The output - Previous:

Updated: