删除'-'之后的字符串，并分组其余的字符串和枢轴列 [英] Remove the string after '-' and group remaining string and pivot column
问题描述
这是我在Excel中的实际数据，可以在C#Windows应用程序的DataGridView中成功读取.
Here is my actual data in Excel, which I am successfully able to read in DataGridView in C# Windows Application.
Test | Energy |
---------------------
C018-3L-1 | 113 |
C018-3L-2 | 79 |
C018-3L-3 | 89 |
C018-3L-4 | 90 |
C018-3L-5 | 95 |
C021-3T-1 | 115 |
C021-3T-2 | 100 |
但是现在我要从Excel文件中以格式设置下面的DataGridView中的数据:
But now I want this data in DataGridView in below Format from excel file:
Test |Energy-1|Energy-2|Energy-3 |
------------------------------------
C018-3L |113 |79 |89 |
C018-3L |90 |95 |NULL |
C021-3T |115 |100 |NULL |
这是我的代码:
private void TensileEnergyData_Load(object sender, EventArgs e)
{
try
{
string sourcefilepath = ConfigurationManager.AppSettings["FilePath"].ToString();
string[] files = Directory.GetFiles(sourcefilepath, "*.xlsx");
foreach (string s in files)
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + s + ";Extended Properties='Excel 12.0;HDR=YES';";
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
connection.Open();
da = new OleDbDataAdapter("Select Test, Energy FROM [Sheet1$]", connection);
da.Fill(dtExcelData);
connection.Close();
}
}
}
catch (Exception ex)
{
objDAL.SendExcepToDB(ex, "TensileEnergyData_Load");
MessageBox.Show("Fail to read data...!!");
}
dataGridView1.Visible = true;
dataGridView1.DataSource = dtExcelData;
}
如何使用分组依据来实现?
How can I achieve this using Group By?
推荐答案
我将提供基于SQL Server的答案，作为您的相关问题问了这个.在这里，您根本没有使用[sql-server]标记问题.希望这对您有所帮助...
I'll provide a SQL-Server based answer, as your very related question asked for this. Here you did not tag your question with [sql-server] at all... Hope this helps...
这是一个很好的理由，为什么您永远不要在一个列中放置多个内容.将此存储在单独的列中，这会容易得多.
This is a very good reason, why you should never ever put more than one content in one column. Store this in separate columns and this will be much easier.
更进一步，这闻起来有点……这些问题应该在您的表示层中解决.
Further more, this smells a bit... Such issues should rather be solved in your presentation layer.
尽管如此，还是可以做到的:
Nevertheless this can be done:
DECLARE @tbl TABLE(Test VARCHAR(100),Energy INT);
INSERT INTO @tbl VALUES
('C018-3L-1',113)
,('C018-3L-2',79)
,('C018-3L-3',89)
,('C018-3L-4',90)
,('C018-3L-5',95)
,('C021-3T-1',115)
,('C021-3T-2',100);
SELECT p.*
FROM
(
SELECT B.Code
,(B.Number-1)/3 AS Line
,CONCAT('Energy-',CASE B.Number % 3 WHEN 0 THEN 3 ELSE B.Number % 3 END) AS ColumnName
,Energy
FROM @tbl t
CROSS APPLY(SELECT LEN(t.Test) - CHARINDEX('-',REVERSE(t.Test))) A(PosLastHyphen)
CROSS APPLY(SELECT LEFT(t.Test,PosLasthyphen) AS Code
,CAST(SUBSTRING(t.Test,PosLastHyphen+2,10) AS INT) AS Number) B
) tbl
PIVOT
(
MAX(Energy) FOR ColumnName IN([Energy-1],[Energy-2],[Energy-3])
) p
ORDER BY Code,Line;
结果
+---------+------+----------+----------+----------+
| Code | Line | Energy-1 | Energy-2 | Energy-3 |
+---------+------+----------+----------+----------+
| C018-3L | 0 | 113 | 79 | 89 |
+---------+------+----------+----------+----------+
| C018-3L | 1 | 90 | 95 | NULL |
+---------+------+----------+----------+----------+
| C021-3T | 0 | 115 | 100 | NULL |
+---------+------+----------+----------+----------+
一些解释
我使用CROSS APPLY来计算您的代码与运行编号之间的距离.然后，我使用整数除法来计算组，并使用模运算符% 将其分布在三列中.
I use the CROSS APPLY to compute the separation of your code and the running number. Then I use the integer division to calculate the group and the modulo operator % to spread this in three columns.
这篇关于删除'-'之后的字符串，并分组其余的字符串和枢轴列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
