CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2014
Sinoy Devassy
Sinoy Devassy

Reputation: 574

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

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 enter image description here

But i need to get the result as enter image description here

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

Upvotes: 0

Views: 80

Answers (1)

AT-2017
AT-2017

Reputation: 3149

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:

Demo

Updated:

Demo_02

Upvotes: 1

Related Questions