如何在SQL Server中将数据收集到单行 [英] How to collect data to single row in sql server

问题描述

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

I have table Risks and history table Risks_history. In Risks table i have data like below:

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

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

So here is we can see that one deal can have several Risks. I need to save data of table Risks in history like below:

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

我需要执行此操作的触发器。但是现在我不能。

I need trigger that will do it. But now I cant.

如何将多行数据收集成一行？

How I can collect data from several rows into one row?

编辑：

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

I need trigger, so now i have below trigger:

      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 中，我有如下数据（触发条件如下） ：

But in table Risks_history i have data(which trigger wrote) like below:

 - 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 |
-------------------------------------

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

But I need to write all Risks whatever risk changing

推荐答案

请尝试一下。

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表的列名，

Your modified trigger and assuming DealID and Description are column names of Risks tables,

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

这篇关于如何在SQL Server中将数据收集到单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！