在 T-SQL 中创建快捷方式函数

Question

使用下面的代码，我可以根据 select 语句的结果创建一个逗号分隔的结果列表：

DECLARE @lst NVARCHAR(MAX) = ''
SELECT @lst = COALESCE(NULLIF(@lst,'')+N',',N'') +  [colName]
FROM [tableName]

我想将其转换为一种快捷功能，例如：

SELECT dbo.fnColAsList([colName]) 
FROM [tableName]

这可能吗？

---

编辑：

举个例子，如果 select 语句返回以下 3 列：

SELECT [colName] FROM [tableName]
>>>  colName
     -----------
[1]  foo1
[2]  foo2
[3]  foo3

然后代码将生成一个 varchar 字段：

SELECT @lst = COALESCE(NULLIF(@lst,'')+N',',N'') + [colName] 
FROM [tableName];
SELECT @lst
>>> lst
    ---------
[1] foo1,foo2,foo3

我希望生成一个快捷方式，以便我可以调用任何列周围的代码，例如我可以这样做：

SELECT fnColAsList(colName) AS 'x' FROM [tab1];
>>>  x
     -----------------
[1]  tab1Col1,tab1Col2,tab1Coln

SELECT fnColAsList(colName) AS 'y' FROM [tab2];
>>>  y
     -----------------
[1]  tab2Col1,tab2Col2,tab2Coln

---

回答：

CREATE FUNCTION dbo.fnColAsList(@rawxml XML)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN STUFF(((SELECT ',' + AllAttribs.value(N'.','nvarchar(max)')
               FROM @rawxml.nodes('/row/@*') x (AllAttribs)
               FOR XML PATH(''),ROOT('x'),TYPE).value('/x[1]','VARCHAR(MAX)') 
              ),1,1,'');
END
GO
SELECT dbo.fnColAsList((SELECT [colName] FROM tabName FOR XML RAW))

Answer 1

你可以尝试一个技巧：通过将 SELECT 作为 XML 进行包装，将其包装在 paranthesis 中。除了纯 SQL 之外，XML 在处理泛型名称方面要强大得多：

CREATE FUNCTION dbo.TestCSV(@SelectForXMLRaw XML)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN STUFF(
                 (
                    SELECT ',' + AllAttribs.value(N'.','nvarchar(max)')
                    FROM @SelectForXMLRaw.nodes('/row/@*') x(AllAttribs)
                    FOR XML PATH(''),TYPE
                 ).value(N'.','nvarchar(max)'),1,1,''
                );
END
GO
DECLARE @tbl TABLE(ID INT IDENTITY,col1 VARCHAR(100),col2 VARCHAR(100),col3 VARCHAR(100));
INSERT INTO @tbl VALUES ('1a','2a','3a'),('1b','2b','3b');
SELECT ID
      ,dbo.TestCSV((SELECT * FROM @tbl AS t2 WHERE t2.ID=t.ID FOR XML RAW)) AS Concatenated
FROM @tbl AS t
GO
DROP FUNCTION dbo.TestCSV;

结果

ID  Concatenated
1   1,1a,2a,3a
2   2,1b,2b,3b

更新 根据您的编辑

从您的编辑中，我采取，您需要将其用于行数据。实际上，您可以使用相同的功能来实现这一点。试试这个

DECLARE @tbl2 TABLE(SomeUnknownColumn VARCHAR(100));
INSERT INTO @tbl2 VALUES ('Val 1'),('Val 2'),('Val 3');
SELECT dbo.TestCSV((SELECT * FROM @tbl2 FOR XML RAW)) AS Concatenated

结果：

Val 1,Val 2,Val 3