CODEWITHSUNDEEP
sqlsql-servervb.net
James
James

Reputation: 567

VB .NET Populate DataGridView using SQL

I was wondering if someone would be willing to help me as I am struggling to work out how to solve the issue I am having.

Below I will give you an example of what I am trying to achieve.

This is a rough example of a table inside my 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 what I am trying to achieve is to group all of the States into the product using DataGridView.

An example below is how I would like the DataGridView to display the data:

+----------+---------+----------+-------+----------+
| Product  | Waiting | Building | Break | Engineer |
+----------+---------+----------+-------+----------+
| Product1 |      19 |       15 |    21 |        5 |
| Product2 |      10 |       26 |     5 |        0 |
+----------+---------+----------+-------+----------+

So basically there is only 1 line per product and then the State column is added up using SUM.

I have no problems with code to enter stuff into DataGridView, I am comfortable bringing stuff into DGV using SQL, however I am struggling to see how I can group these products together and display it like the table above.

I have tried using DISTINCT and SUM through SQL to try and achieve this but had no luck.

If someone could point me in the right direction I would really appreciate it.

Thanks guys.

Upvotes: 1

Views: 414

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try this Query

SELECT * FROM
(
    SELECT PRODUCT,
           CASE WHEN State = 'Waiting' Then Sum(TotalTime) OVER (PARTITION BY State,Product ORDER BY (select Null)) Else 0 END Waiting,
           CASE WHEN State = 'Building' Then Sum(TotalTime) OVER (PARTITION BY State,Product ORDER BY (select Null)) Else 0 END Building,
           CASE WHEN State = 'Break' Then Sum(TotalTime) OVER (PARTITION BY State,Product ORDER BY (select Null)) Else 0 END Break,
           CASE WHEN State = 'Engineer' Then Sum(TotalTime) OVER (PARTITION BY State,Product ORDER BY (select Null)) Else 0 END Engineer
    From MyTable
) AS T 
    Group By Product,Engineer,Break,Waiting,Building

Set this as DataSource for your DataGrid.

Upvotes: 3

Related Questions