VB .NET Populate DataGridView using SQL

Question

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.

ok dear you want to use grouping in sql or you want it in DGV — Developerzzz
– Developerzzz, Commented Mar 20, 2014 at 9:36
Thanks for your response :). I want to group it in the DGV, although I am assuming I need to do the grouping through the SQL query first to output it correctly to my DGV? — James
– James, Commented Mar 20, 2014 at 9:41

Vignesh Kumar A · Accepted Answer · 2014-03-20 10:24:31Z

3

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.

edited Mar 20, 2014 at 10:24

answered Mar 20, 2014 at 9:38

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Over a year ago

When I try to run this SQL query through Management Studio, I get an Incorrect Syntax message... Column 'line_log.dbo.Line1Log.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Vignesh Kumar A Over a year ago

@James can you post the query with table structure in sqlfiddle

James Over a year ago

@VigneshKumar thanks again for your response, the new query doesn't work for me either, it does not like the fields Waiting, Building, Break etc without the ''. I have put an example of how the table is currently setup on sqlfiddle.com/#!2/1e601/1 hopefully this might clear things up. Thanks so much for your help thus far, I do appreciate it.

Vignesh Kumar A Over a year ago

@James Ill try agin for you, if this dint help then dont mark it as answer because you won't get others help

Collectives™ on Stack Overflow

VB .NET Populate DataGridView using SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related