Шыназ Алиш Шыназ Алиш - 1 month ago 10
SQL Question

How to collect data to single row in sql server

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?

EDIT:

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;


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

Answer

Please try this..

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

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