如何在SQL Server中将数据收集到单行

小编典典

如何在SQL Server中将数据收集到单行

sql

我有风险表和历史记录表 Risks_history 。在“风险”表中，我有如下数据：

-----------------------------
| ID | DealID | Description |
-----------------------------
| 1  | 14     | Risk1       |
-----------------------------
| 2  | 14     | Risk2       |
-----------------------------
| 3  | 14     | Risk3       |
-----------------------------
| 4  | 15     | Risk4       |
-----------------------------

因此，我们可以看到一笔交易可以有多个风险。我需要在历史记录中保存表 Risks的 数据，如下所示：

-------------------------------------
| ID | dealID |   AllDescriptions   |   
-------------------------------------
| 1  | 14     | Risk1, Risk2, Risk3 |
-------------------------------------
| 2  | 15     | Risk4               |
-------------------------------------

我需要触发器来做到这一点。但是现在我不能。

如何将数据从多行收集到一行？

编辑：

我需要触发器，所以现在我有以下触发器：

      INSERT INTO [dbo].[Risks_history]
      (
            DealID,
            [AllDescription]
      )
      SELECT 
            [DealID],
            stuff((select ',' + i.name from inserted i
                   where i.DealID= i2.DealID
                   FOR XML PATH('')),1,1,'') as Description                                          
        FROM inserted i2;

但是在 Risks_history 表中，我有如下数据（触发条件如下）：

 - When I change Risk1:

    -------------------------------------
    | ID | dealID |   AllDescriptions   |   
    -------------------------------------
    | 1  | 14     | Risk1, Risk1, Risk1 |
    -------------------------------------

 - When I change Risk2:

-------------------------------------
| ID | dealID |   AllDescriptions   |   
-------------------------------------
| 1  | 14     | Risk2, Risk2, Risk2 |
-------------------------------------

 - When I change Risk3:

-------------------------------------
| ID | dealID |   AllDescriptions   |   
-------------------------------------
| 1  | 14     | Risk3, Risk3, Risk3 |
-------------------------------------

但是无论风险如何变化，我都需要写所有风险

阅读 152

2021-05-16

共1个答案

小编典典

请尝试这个。

SELECT DISTINCT
      DealID
    , STUFF((
        SELECT N', ' + CAST([Description] AS VARCHAR(4000))
        FROM Risks R2
        WHERE R1.DealID = R2.DealID
        FOR XML PATH (''), TYPE), 1, 2, '') AS AllDescriptions   
FROM Risks R1
GROUP BY DealID

修改后的触发器并假设DealID和Description是Risks表的列名称，

DECLARE @DealID INT;

SELECT 
    @DealID = [DealID]                                         
FROM inserted;

INSERT INTO [dbo].[Risks_history]
      (
            DealID,
            [AllDescription]
      )
      SELECT 
            [DealID],
            STUFF((
                SELECT ', ' + R2.[Description]
                FROM Risks R2
                WHERE R1.DealID = R2.DealID
                FOR XML PATH (''), TYPE), 1, 2, '') AS AllDescriptions                                        
        FROM Risks R1
        WHERE DealID = @DealID
    GROUP BY DealID

2021-05-16