Sql SERVER 2012 select from column without knowing the name of that last

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Please read till the end
Hello everyone I'm beginner on the ado.net and I want bring data to show it on my Data Grid View so i have two table the structure of the first table is,including values :
idrubrique namerubrique

1 salary
2 trans
3 panier
4 congé

and the structure of the second table is,including values :
idemployee employee salary trans panier congé
1 thales 125 25 24 62
2 interime 254 87 12 34
3 aymane 524 25 45 47

to which i want it to be shown on the Dat Grid View is like that
idemployee employee namerubrique montant
1 thales salary 125
1 thales trans 25
1 thales panier 24
1 thales congé 62 ,the same for all employee.
the resons why i dont insert that in one table from the beginning is for the namerubrique may change at any tame caus it's entered by the user of the application.
I hope you can understand me.
you can help by syntax in sql server and then i will use it in c#
in the code bellow which i tried you will find same names of other tables and columns they didn't mater.
Thanks in advance.

What I have tried:

string nomrubrique = string.Empty;
            int count;
            DataTable dt2 = new DataTable();
            dt2.Clear();
            cnx.Open();
            {
                SqlCommand cmd = new SqlCommand("select nomrubrique from rubrique", cnx);
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                SqlCommand cmd1 = new SqlCommand("select * from paie where  paie.nomclient='" + comboBox1.SelectedValue + "'", cnx);
                SqlDataReader dr1 = cmd1.ExecuteReader();
                DataTable dt1 = new DataTable();
                dt1.Load(dr1);
                {
                    SqlCommand cmd4 = new SqlCommand("select count (*) from rubrique",cnx);
                    cmd4.ExecuteNonQuery();
                    count =(int) cmd4.ExecuteScalar();
                    for (int i=1;i<count;i++)
                    {
                        SqlCommand cmd3 = new SqlCommand("select nomrubrique from rubrique WHERE numrubrique="+i+"",cnx);
                        cmd3.ExecuteNonQuery();
                        nomrubrique = (string)cmd3.ExecuteScalar();
                        foreach (DataRow row in dt.Rows)
                        {
                            foreach (DataRow row1 in dt1.Rows)
                            {
                                SqlCommand cmd2 = new SqlCommand("select distinct client.nomclient,adresse,ice,matricule,nomemp,prenomemp,nomrubrique," + row1[nomrubrique] + " as coef," + row1[nomrubrique] + " * coef as total from paie join client on paie.nomclient=client.nomclient join contrat on client.codeclient=contrat.codeclient join rubrique on contrat.numrubrique=rubrique.numrubrique where client.nomclient='" + comboBox1.SelectedValue + "' and paie.nomclient='" + comboBox1.SelectedValue + "' and nomrubrique='" + row[nomrubrique] + "'", cnx);
                                SqlDataReader dr2 = cmd2.ExecuteReader();

                                dt2.Load(dr2);

                            }
                        }
                    }
                }
            }
            dataGridView1.DataSource = dt2;
            cnx.Close();

Posted 15-Mar-18 12:40pm

Aymane HATAFI

Updated 16-Mar-18 0:03am

Add a Solution

Comments

Tomas Takac 15-Mar-18 20:03pm

First, you do not so many queries. You are querying table rubrique three times to get nomrubrique, then the count, then nomrubrique again. This is not necessary.

Second, you should store your data differently. Keep your rubrique table. Remove columns salary, trans, panier, congé from employee table. Create a new table rubrique_employe with columns idemployee, idrubrique, montant. That should solve your problems.

Aymane HATAFI 16-Mar-18 7:10am

the second table which include columns salary and panier and trans and also congé is static cause it will be import from an excel file so that i don't have permission to modify it

3 solutions

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)

an0ther1 · Answer 1 · 2018-03-15T14:16:00

Solution 1

To get the structure you want you will need to use UNION - refer; UNION (Transact-SQL)[^]

Your transact would be as follows;

SQL

SELECT idemployee, employee, 'salary' AS namerubrique, salary
FROM Employee
UNION
SELECT idemployee, employee, 'trans' AS namerubrique, trans
FROM Employee
UNION
SELECT idemployee, employee, 'panier' AS namerubrique, panier
FROM Employee
UNION
SELECT idemployee, employee, 'congé' AS namerubrique, congé 
FROM Employee

NOTE: If you want to order the columns, add the Order By statement after the last SELECT statement

Kind Regards

Posted 15-Mar-18 14:16pm

an0ther1

Comments

Aymane HATAFI 16-Mar-18 7:08am

Thanks a lot bro that work for me but i think it will work just for same time cause when the user will enter a new namerubrique the this will not work anymore so i need something like a for or something like that which will show all namerubrique existing on table rubrique

an0ther1 18-Mar-18 17:22pm

Both Maciej & my solution have the same problem, but neither of them is dependent on the idrubrique column value.
When a user adds a new namerubrique how does a new column get created in the Employee table?

Maciej Los 16-Mar-18 8:36am

UNION? I'd prefer to use UNPIVOT in this case. See my answer.

an0ther1 18-Mar-18 17:23pm

Fair call Maciej - have 5ed your answer. Nice solution

Maciej Los 18-Mar-18 17:26pm

Thank you.

GenJerDan · Answer 2 · 2018-03-16T00:04:00

Solution 2

Personally, I would create a view on the SQL server, then query and display the data from that, rather than trying to match things up on the fly in code.

Posted 16-Mar-18 0:04am

GenJerDan

Comments

Aymane HATAFI 16-Mar-18 7:12am

i can create a view it's optimized method "thanks for this information" but that wouldn't solve problem

Maciej Los · Answer 3 · 2018-03-16T02:28:00

All what you need is to use UNPIVOT[^]. See:

SQL

DECLARE @second TABLE(idemployee INT IDENTITY(1,1), employee NVARCHAR(30), salary INT, trans INT, panier INT, congé INT)
INSERT INTO @second (employee, salary, trans, panier, congé)
VALUES('thales', 125, 25, 24, 62), 
('interime', 254, 87, 12, 34), 
('aymane', 524, 25, 45, 47)

SELECT idemployee, employee, namerubrique, montant
FROM @second  AS pvt
UNPIVOT (montant FOR namerubrique IN ([salary], [trans], [panier], [congé])) AS unpvt

C#

DataTable second = new DataTable();
second.Columns.AddRange(new DataColumn[]
	{new DataColumn("idemployee", typeof(int)),
	new DataColumn("employee", typeof(string)),
	new DataColumn("salary", typeof(int)),
	new DataColumn("trans", typeof(int)),
	new DataColumn("panier", typeof(int)),
	new DataColumn("congé", typeof(int))});
second.Rows.Add(new object[]{1, "thales", 125, 25, 24, 62}); 
second.Rows.Add(new object[]{2, "interime", 254, 87, 12, 34});  
second.Rows.Add(new object[]{3, "aymane", 524, 25, 45, 47}); 

string[] cols = new string[]{"salary","trans", "panier", "congé"};

var unpivot = second.AsEnumerable()
	.SelectMany(x=> cols.Select(c=> new
		{
			idemployee = x.Field<int>("idemployee"),
			employee = x.Field<string>("employee"),
			namerubrique = c,
			montant = x[c]
		}));

In both cases, result is the same:

idemployee	employee	namerubrique	montant
1			thales		salary			125
1			thales		trans			25
1			thales		panier			24
1			thales		congé			62
2			interime	salary			254
2			interime	trans			87
2			interime	panier			12
2			interime	congé			34
3			aymane		salary			524
3			aymane		trans			25
3			aymane		panier			45
3			aymane		congé			47

God luck!

By The Way: Your code is SQL Injection[^] vulnerable!
You should avoid of using concatenated string as a query(ies)!
How To: Protect From SQL Injection in ASP.NET[^]
Writing Secure Dynamic SQL in SQL Server | Microsoft Docs[^]

Sql SERVER 2012 select from column without knowing the name of that last

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us