Sql SERVER 2012从列中选择而不知道最后一个的名称 [英] Sql SERVER 2012 select from column without knowing the name of that last
问题描述
请阅读到最后
大家好我是ado.net的初学者，我想把数据显示在我的数据网格视图上，所以我有两个表格的第一个结构表是，包括价值：
idrubrique namerubrique
1薪水
2 trans
3 panier
4congé
和第二张表的结构，包括价值：
idemployee employee salary transpaniercongé
1 thales 125 25 24 62
2 interime 254 87 12 34
3 aymane 524 25 45 47
我希望它在Dat Grid View上显示就像那样
idemployee employee namerubrique montant
1泰勒斯薪水125
1 thales trans 25
1 thales panier 24
1thalescongé62，对所有员工来说都是一样的。
为什么我不从一开始就在一张桌子中插入这些名字，因为namerubri可能会改变应用程序的用户输入的任何驯服。
我希望你能理解我。
你可以通过sql server中的语法帮助然后我将在c＃
在我试过的代码中，你会发现他们没有的其他表和列的相同名称。
提前谢谢。
我尝试过：
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();
推荐答案
要获得所需的结构，您需要使用UNION - refer; UNION（Transact-SQL） [ ^ ]
您的交易将如下;
To get the structure you want you will need to use UNION - refer; UNION (Transact-SQL)[^]
Your transact would be as follows;
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
注意：如果您想订购列，在最后一个SELECT语句之后添加Order By语句
问候语
NOTE: If you want to order the columns, add the Order By statement after the last SELECT statement
Kind Regards
就个人而言，我会在SQL服务器上创建一个视图，然后查询并显示数据，而不是试图在代码中动态匹配。
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.
你需要的只是使用UNPIVOT [ ^ ]。请参阅：
All what you need is to use UNPIVOT[^]. See:
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
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
上帝好运！
顺便说一下：你的代码是 SQL注入 [ ^ ]易受攻击！
你应该避免使用串联字符串作为查询！
如何：防止ASP.NET中的SQL注入 [ ^ ]
在SQL Server中编写安全动态SQL Microsoft Docs [ ^ ]
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从列中选择而不知道最后一个的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
