从字符串中删除'-'之后的字符，并将其余字符串分组 [英] Remove character after '-' from string and group that remaining string together

问题描述

这是我在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     |
C021-3T-1  | 115    |
C021-3T-2  | 100    |

但是现在我想要在DataGridView中使用以下格式的数据:

But now I want this data in DataGridView in below Format:

Test    |Energy-1|Energy-2|Energy-3 |
------------------------------------
C018-3L |113     |79      |89       |
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方法:

This is marked with [sql-server]. This is a SQL-Server approach:

您可以尝试

我创建一个样机表

DECLARE @tbl TABLE(Test VARCHAR(100),Energy INT);
INSERT INTO @tbl VALUES
 ('C018-3L-1',113)
,('C018-3L-2',79)
,('C018-3L-3',89)
,('C021-3T-1',115)
,('C021-3T-2',100);

这是查询

SELECT p.*
FROM
(
    SELECT LEFT(t.Test,7) AS Test
          ,CONCAT('Energy-',RIGHT(t.Test,1)) AS ColumnName
          ,Energy
    FROM @tbl t
) tbl
PIVOT
(
    MAX(Energy) FOR ColumnName IN([Energy-1],[Energy-2],[Energy-3])
) p;

结果

Test        Energy-1    Energy-2    Energy-3
C018-3L     113         79          89
C021-3T     115         100         NULL

一些解释:

这取决于您的字符串的固定格式，最多3列.另一种方法是搜索最后一个连字符，并使用其位置在开头部分和数字之间分割字符串.使用不同数量的数字，您必须知道预期的最大值，或者必须完全走一条通用路线(例如动态SQL).

This is relying on a fix format of your string and a maximum of 3 columns. Another approach was to search for the last hyphen and use its position to split the string in the leading part and the number. With a varying count of numbers you must either know the expected maximum or you must walk a generic route entirely (e.g. dynamic SQL).

这篇关于从字符串中删除'-'之后的字符，并将其余字符串分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！