在更新期间将多行连接成逗号分隔的值

Question

我有一个临时表，其中有一个名为 Method 的字段，因此：

DECLARE @CaseSites TABLE (
 BriefID  int,
 Method  varchar(60)
 -- other fields
)

Method 将从另一个表 - CaseEventTypeList 中的几行填充。

运行

SELECT * FROM CaseEventTypeList WHERE RefID = 1

Gives

RefID TypeID
1  2
1  3
1  6

将其转换为单个逗号分隔的结果相当简单：

DECLARE @CETList varchar(30)

SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar)
FROM CaseEventTypeList CETL 
WHERE CETL.RefID = 1

PRINT @CETList

Giving：

2,3,6

现在我需要扩展它以容纳整个表。这就是我想到的：

UPDATE @CaseSites SET Method = COALESCE(Method + ',','') + CAST(CETL.TypeID AS VARCHAR)
 FROM CaseEvents CE
   JOIN CaseEventTypeList AS CETL ON CETL.RefID = CE.TypeListID
 WHERE BriefID = CE.CaseID

但是，这只用每组值中的第一个值填充方法。

我在网上查找并发现了这个，但宁愿不使用 udf - 特别是当解决方案感觉如此接近时。

更新：数据相当简单，RefId 会针对每种情况递增，TypeID 可以是任意数字，尽管当前仅对 1 到 8 进行建模。因此你可能会：

RefID TypeID
12  2
12  7
13  1
14  1
14  3
14  6

并且这将有望被建模为

SELECT Method from @CaseSites
Method 
...
12  2,7
13  1
14  1,3,6
...

Answer 1

我认为您的问题是因为更新语句仅对每行一次“SET Method =”求值一次，因此您只能在列表中获得一个值。

UDF 是执行此操作的简单方法，但由于您使用的是临时表，因此这可能不是一个选择，并且您无论如何都希望避免使用它们。因此，您可能需要使用光标（不太好），但可以按照您想要的方式完成工作。

这是我根据你原来的sql想出的。

DECLARE myCURSOR Cursor
FOR
  Select BriefID
  from #CaseSites

Open myCursor
DECLARE @BriefID int
DECLARE @CETList varchar(30)
Fetch NEXT FROM myCursor INTO @BriefID
While (@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)

  SET @CETList = ''
  SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar)
    FROM #CaseEventTypeList CETL 
   WHERE CETL.RefID = @BriefID 

  UPDATE #CaseSites
     SET Method = @CETList
   WHERE BriefID = @BriefID

  Fetch NEXT FROM myCursor INTO @BriefID
END
CLOSE myCursor
DEALLOCATE myCursor 

Answer 2

如果您可以使用 xml，我找到了比第一个更好的答案：
使用 xml 的相关子查询。

UPDATE #CaseSites 
   SET Method = (   
                  select  cast([TypeID] as varchar(30))+ ',' 
                    from #CaseEventTypeList
                   where RefID = CE.CaseID 
                     for xml path ('') 
                )
  FROM #CaseEvents CE