不知道列名如何将SQL select的行存入字符串变量中

Question

我是SQL编写的新手，非常希望在这个问题上得到帮助。 :)

我正在尝试将整行数据选择为一个字符串，最好用空格或逗号分隔。我希望以一种通用的方式实现这一目标，而不需要了解表中列的具体信息。

我想做的是这样的：

DECLARE @MyStringVar NVARCHAR(MAX) = ''
@MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING

但是我最终做的是这样的：

DECLARE @MyStringVar = ''
DECLARE @SecificField1 INT
DECLARE @SpecificField2 NVARCHAR(255)
DECLARE @SpecificField3 NVARCHAR(1000)
...
SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID
SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' +  @Field2 + ' ' + @Field3

糟糕。:(

我看到一些人发布了关于COALESCE函数的内容，但我没有看到任何人在不使用特定列名的情况下使用它。

此外，我在想，也许有一种方法可以动态地使用列名，通过获取它们：

SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' 

看起来这似乎不应该那么复杂。:(

我的方法目前可以使用，但提前感谢任何能指向更好解决方案的人。:)

编辑： 已经修复，感谢所有回答的人。:)

- Brandi

---

你能解释一下你的最终目标是什么吗？如果我们更好地理解了这一点，也许我们就能提出更好的解决方案。 - AdaTheDev

我也很好奇这样一个通用程序的目的。在生产代码中，我不会使用任何这样的解决方案。 - Thomas

我的总体目标是跟踪数据库中的删除操作（通过触发器）并将更改写入日志文件。我需要字符串，因为我正在尝试将一行作为参数传递给一个DLL函数（用C#编写，以字符串形式接收行），该函数将删除的数据写入日志文件。我希望采用通用方法，因为我可能需要为数据库中的每个表编写此触发器。不幸的是，msdn页面说DDL触发器无法跟踪行删除，因此似乎我必须一遍又一遍地编写此触发器。如果是这种情况，我至少希望能够动态调整到对表所做的任何更改。 - Brandi

@Brandi，我会手动编写触发器以提高速度（针对每个需要和数据类型编写每个触发器的确切列和必要格式）。为什么要在每次触发器启动时查找列的开销呢？您可以轻松修改我的答案中使用 INFORMATION_SCHEMA.COLUMNS 生成每个表的 INSERT 代码的查询。 - KM.

首先，我假设您想跟踪DML而不是DDL。上述解决方案对DDL无效。SQL Server（2k、2k5、2k8）触发器绝对会在删除时触发。在上述解决方案中，如果列顺序更改，输出将混乱不堪。坦白地说，我认为编写一个脚本来编写触发器（或使用第三方程序或2k8的更改跟踪）比使用此方法更好，但我不知道您问题的所有细节。 - Thomas

@Thomas：是的，我正在使用DML触发器...我只是说，我希望能够编写一个整个数据库的删除触发器，而不是在每个表上都要这样做。这就是我想要更通用方法的原因。此外，如果表中的任何列被添加/删除/更改，我也不想重新编写触发器。至于顺序，我修改了KM的代码，使其输出该行中每个值的“ColumnName: Value”。 - Brandi

Answer 1

试一试这个：

DECLARE @SQL nvarchar(MAX), @YourTable nvarchar(200)
SET @YourTable='YourTableName'
SELECT @SQL=
    STUFF(
             (
                  SELECT
                  ' + '','' + COALESCE(''''''''+CONVERT(NVARCHAR(MAX),['+c.COLUMN_NAME+']'+CASE WHEN c.DATA_TYPE='datetime' THEN ',121' ELSE '' END+')+'''''''',''null'')'
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_NAME = 'ap_voucher'
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH('')
             ), 1, 9, ''
         )
SET @SQL = 'SELECT ' + @SQL + ' FROM '+@YourTable
exec (@SQL)

样例输出：

---------------------------------------------------------------------------
'030',null,'I','Zzz0',null,'1000021111          ','2000-03-01 00:00:00.000'
'001',null,'I','zzz0',null,'900099618           ','1999-12-03 00:00:00.000'
'001',null,'I','ET10',null,'KOU557              ','1999-11-01 00:00:00.000'
'001',null,'I','Mzzz',null,'017288              ','1999-11-12 00:00:00.000'

- KM.

---

很棒的答案 - 这是我一直在寻找的方法。是否可以添加一个条件来控制输出哪些行？理想情况下，我希望得到这个结果，但加上条件'WHERE ID=@ID'。 - Brandi

@Brandi，只需完成最终的SET命令：SET @SQL = 'SELECT ' + @SQL + ' FROM '+@YourTable+' WHERE ID='+CONVERT(nvarchar(100),@ID)，然后你就可以开始了。 - KM.

Answer 2

要动态地执行这个操作，只需要知道表名，你可以使用以下方法：

DECLARE @nSQL NVARCHAR(MAX)
SELECT @nSQL = COALESCE(
     @nSQL + ' + '','' + CAST(ISNULL([' + c.COLUMN_NAME + '], '''') AS NVARCHAR(MAX))', 
    'CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX))')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'TestTable'

SET @nSQL = 'SELECT ' + @nSQL + ' FROM YourTable'

EXECUTE sp_executesql @nSQL

不过，这取决于你的最终目标是什么。

这将处理列中的空值，但是（例如）NULL整数会变成0。

- AdaTheDev

---

您应该为生成的 @nSQL 中的每个字段添加 CONVERT 和 COALESCE，以便数字和空列不会破坏最终的连接。例如：Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ',' to data type int.（将 varchar 值“,”转换为 int 数据类型时转换失败） - KM.

@KM - 干杯，干得好！我本来想处理整数/空值等的，但是我把CAST放在了COLUMN_NAME周围而不是字符串内部执行！已经更正了SQL。 - AdaTheDev

看我的答案，我使用了一种不同的列连接技术，可以防止对列值进行重复格式化。我还引用了值，格式化日期时间，并显示NULL值... - KM.

Answer 3

翻译成中文：

简短的回答是你不能这样做。

你能做的唯一的事情就是类似于你已经有的。

SELECT @MyField1 = MyField1, @MyField2= MyField2...etc FROM MyTable

然后你可以连接这些字段：

SELECT @MyResult = (SELECT @MyField1 + @MyField2 + MyField3...)

SELECT @MyResult

- JonH

---

为什么不使用SELECT MyField1 + MyField2 + MyField3 FROM Table呢？ - user76035

@wwosik，我只是在使用变量，因为OP后面的步骤可能需要这些变量，但这也是合理的。 - JonH

是的，不知道查询结果列是什么听起来有点像创建一个非常通用的访问方法。或者更确切地说是一个Debug.Print。 - user76035

Answer 4

如果您可以处理XML格式的输出，类似这样的东西可能会起作用：

DECLARE @MyStringVar NVARCHAR(MAX) = ''
SET @MyStringVar = (SELECT * FROM MyTable WHERE ID = @ID FOR XML RAW)

SELECT @MyStringVar 

作为额外的奖励，如果您的数据适合，您可以使用以下方法去除XML格式而不是SELECT @MyStringVar:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(@MyStringVar, '<row ', ''), '="', ' '), '" ', ', '), '"/>', '')

如果您的数据包含在标签中使用的任何字符，那么您需要进行一些额外的转换，但这应该可以让您接近所需的结果。

---

刚刚注意到，似乎您实际上不希望列名以及数据一起显示。在这种情况下，其他答案中的信息模式查询之一将是获得所需内容的最佳方法。

- mwigdahl

Answer 5

如果目标是更改跟踪，则建议不要像KM建议的那样使用串联。

1. 列的顺序必须保持不变。即使列的顺序略微改变，值的含义也最多是模糊的，或者更有可能会丢失。建议永远不要依赖列的顺序。

2. 随着表变得越来越宽和/或包含大量数据，串联将变得越来越慢。SQL Server以有效串联而闻名。此外，想象一下具有几十个nvarchar（max）字段且每个字段都有2000个字符的表。这将产生一个大约为48K的字符串！

3. 您可能需要更改分隔符。假设我有一个等于"'Pain'，'Suffering'和'Trouble'"的值。这种类型的条目在分析数据时会创建问题。

如果您正在使用SQL Server 2008，则建议使用SQL Server的更改跟踪机制。如果没有使用该机制，则建议购买第三方审计工具。如果这也不是一个选项，那么我会使用触发器将删除的值写入另一个表中。至少，这可以强制您确保审核数据架构与源架构同步。

- Thomas