我的SQL Server数据库中有一个具有以下结构的表：
CREATE TABLE [file].[NumeTestINV](
[Category] [nvarchar](255) NULL,
[Class] [nvarchar](255) NULL,
[Company] [nvarchar](255) NULL,
[Division] [nvarchar](255) NULL,
[Jan] [float] NULL,
[Feb] [float] NULL,
[Mar] [float] NULL,
[Apr] [float] NULL,
[May] [float] NULL,
[Jun] [float] NULL,
[Jul] [float] NULL,
[Aug] [float] NULL,
[Sep] [float] NULL,
[Oct] [float] NULL,
[Nov] [float] NULL,
[Dec] [float] NULL
) ON [PRIMARY]
我正在尝试通过Visual Studio 2019 SSIS脚本任务中的C＃代码检索其列列表。我有以下代码c-sharp代码段。
//Get Matching Column List from SQL Server
string SQLColumnList = "";
SqlCommand cmd = myADONETConnection.CreateCommand();
cmd.CommandText = SQLQueryToGetMatchingColumn;
SQLColumnList = (string)cmd.ExecuteScalar();
MessageBox.Show(" Matching Columns: " + SQLColumnList);
但是，问题是列列表输出的ASCII排序不正确。我不确定为什么我的SQLColumnList没有按照正确的SQL表列顺序列出，而是按照下面的顺序列出？
"[Apr],[Aug],[Category],[Class],[Company],[Dec],[Division],[Feb],[Jan],[Jul],[Jun],[Mar],[May],[Nov],[Oct],[Sep]"
我期待以下输出：
"[Category],[Class],[Company],[Division],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]"
这是SQLQueryToGetMatchingColumn的代码，我的源代码是一个Excel文件，具有与我的物理SQL表相同的顺序的相同列列表：
SQLQueryToGetMatchingColumn = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
TableName + "' and Table_SChema='" + SchemaName + "'" +
"and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";
这是SQLQueryToGetMatchingColumn的输出：
"select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') for xml path('')),1,1,'') AS ColumnList"
CommandText输出如下：
"select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') for xml path('')),1,1,'') AS ColumnList"
我希望这是我的代码片段中的一个小代码更改，并且我不必完全更改检索SQL列列表所用的方法。
如果要按顺序排列列，请尝试如下操作：
select string_agg( quotename(name), ', ') within group (order by columnproperty(c.object_id, c.name, 'ordinal') ) columnList
from sys.columns c
where c.object_id = object_id('[file].[NumeTestINV]')
要么
select string_agg( quotename(column_name), ', ') within group (order by ordinal_position) columnList
from information_schema.COLUMNS
where TABLE_SCHEMA = 'file'
and TABLE_NAME = 'NumeTestINV'
或使用旧的XML连接，
select STUFF((Select ','+ quotename(Column_Name) from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') order by ordinal_position for xml path('')),1,1,'') AS ColumnList
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句