sql server - VB .NET Populate DataGridView using SQL -

i wondering if willing me struggling work out how solve issue having.

below give example of trying achieve.

this rough example of table inside sql database:

+----+----------+----------+-----------+ | id | product  |  state   | totaltime | +----+----------+----------+-----------+ |  1 | product1 | waiting  |        10 | |  2 | product1 | building |        15 | |  3 | product1 | engineer |         5 | |  4 | product1 | break    |        21 | |  5 | product1 | waiting  |         9 | |  6 | product2 | building |        11 | |  7 | product2 | waiting  |        10 | |  8 | product2 | break    |         5 | |  9 | product2 | building |        15 | +----+----------+----------+-----------+ 

now trying achieve group of states product using datagridview.

an example below how datagridview display data:

+----------+---------+----------+-------+----------+ | product  | waiting | building | break | engineer | +----------+---------+----------+-------+----------+ | product1 |      19 |       15 |    21 |        5 | | product2 |      10 |       26 |     5 |        0 | +----------+---------+----------+-------+----------+ 

so there 1 line per product , state column added using sum.

i have no problems code enter stuff datagridview, comfortable bringing stuff dgv using sql, struggling see how can group these products , display table above.

i have tried using distinct , sum through sql try , achieve had no luck.

if point me in right direction appreciate it.

thanks guys.

try query

select * (     select product,            case when state = 'waiting' sum(totaltime) on (partition state,product order (select null)) else 0 end waiting,            case when state = 'building' sum(totaltime) on (partition state,product order (select null)) else 0 end building,            case when state = 'break' sum(totaltime) on (partition state,product order (select null)) else 0 end break,            case when state = 'engineer' sum(totaltime) on (partition state,product order (select null)) else 0 end engineer     mytable ) t      group product,engineer,break,waiting,building 

set datasource datagrid.