Datagridview with multiple sources and columns to rows

Question

0.00/5 (No votes)

See more:

Let me start off by saying I'm not a programmer at all. I'm trying to learn some basic stuff though.

I'm using VS and SQL Server. I've setup my datasources. The basics of doing a "normal" datagridview isn't an issue for me because it's rather intuitive and simple. In this instance I'm totally lost.

I have two database tables, one row in each. I need to create a Datagridview with 5 columns and specified table column values as rows in the datagridview.

Database Table One Columns Example Info:
ID, Desc1, Desc2, Desc3, Desc4, Desc5, Other columns....

Database Table Two Columns Example Info:
ID, Tax1, Tax2, Tax3, Tax4, Tax5, Start1, Start2, Start3, Start4, Start5, End1, End2, End3, End4, End5, OtherColumns....

Tables are joined.

Columns in Datagridview:
Number - This would be autonumber
Description - Row info would come from Database Table One column values Desc1 through Desc5. One per row.
Tax - Row info would come from Database Table Two column values Tax1 through Tax5
Start - Row info would come from Database Table Two column values Start1 through Start5
End - Row info would come from Database Table Two column values End1 through End5

How I want the values displayed in the Datagridview:

NO, DESCRIPTION, TAX, START, END
1, Desc1, Tax1, Start1, End1
2, Desc2, Tax2, Start2, End2
3, Desc3, Tax3, Start3, End3
4, Desc4, Tax4, Start4, End4
5, Desc5, Tax5, Start5, End5

Unfortunately the table layouts cannot be changed as it links to other master tables. For example changing the columns to rows in the database table.

What I have tried:

I honestly don't know what the best way would be to achieve this as the data in the Datagridview for the columns should still be able to be changed and updated.

Any guidance in the right direction would be appreciated.

Posted 24-Feb-21 4:15am

Logan1980

Updated 24-Feb-21 6:19am

v2

Add a Solution

Comments

[no name] 24-Feb-21 10:22am

Showing data from different tables is called 'join'. Of course to join data from different tables one need a relation between the data of the different tables. Here a nice intoduction about joining data:
Visual Representation of SQL Joins[^]

Logan1980 24-Feb-21 10:36am

100% I have no issue with that part of it. The join works perfectly because of the ID column. Thus one of the reasons the layout of the table cannot be changed. My biggest issue is with getting the specified database column values into rows and my specified columns in the datagridview since there's only one row in each table but multiple columns.

[no name] 24-Feb-21 11:04am

Your update of the question makes it more clear. Seems to go into the direction 'pivot' of a result, not my field :-) but be patient there are some SQL specialists around here ;)

Logan1980 24-Feb-21 11:16am

I thought it might. :D I have made use of pivot/unpivot before with something else I played around with so I have an idea of how to accomplish that. However, with that, I'm a bit further puzzled because the data still has to be able to be updated. I no idea how to get around that with unpivot.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2021-02-24T06:19:00

Solution 1

The database design was blown in the first place: repeating groups.

And you say they're "joined"; that still results in "repeating groups". So you should "pivot" the tables (to "normalize" them); then join them; resulting in the data set (table) you require.

And unless you go back and normalize the original tables, it will be a mess to try and "update" them also.

Short story, start over and normalize (eliminate repeating groups) in the original tables. Anything else you do, just compounds the problem.

Posted 24-Feb-21 6:19am

Gerry Schmitz

Comments

Logan1980 24-Feb-21 12:31pm

Thanks Gerry. It confuses me as well WHY the tables would be like this. The layout design isn't specifically my idea. It seems quite a number of tables function the same way. May I ask what you mean by "repeating groups"?

[no name] 24-Feb-21 14:18pm

You have multiple taxes (i.e. "tax types").

Logically, you have a tax type, and an amount; not simply "some tax fields".

These amounts are dependent on the "ID". "Normalizing" them (3rd normal form) results in a table of ID, TaxType, Amount.

A UI would show a parent-relationship between whomever "ID" represents, and the "types of taxes" with amounts that they have.

The advantages are it's easier to query and the "ease" with which one can add / delete / update taxes. The tradeoff with more storage is not worth considering.

If "start / end" is a "dependent" property of of Id + tax type, add it to that table; otherwise add it to a "master" tables of "tax types" (and rate, desc, etc.)

(sorry for the typing error revisions: no "preview")

Logan1980 24-Feb-21 16:02pm

Thanks for the explanation Gerry. Yeah. I'm just redoing these tables as I'm going along now. Ultimately I'll have more tables but at least I can get the information out and updated/inserted/deleted correctly.