Add two nvarchar columns using T-SQL [duplicate]

Question

Table structure

CREATE TABLE [dbo].[StackQuestion]
(
    [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Product_Id] [BIGINT] NULL,
    [Quantity] [DECIMAL](18, 6) NULL,
    [Description] [NVARCHAR](MAX) NULL,

    CONSTRAINT [PK_StackQuestion] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)

Demo data

INSERT [dbo].[StackQuestion] ([ID], [Product_Id], [Quantity], [Description]) 
VALUES (1, 10, CAST(50.000000 AS Decimal(18, 6)), N'Description1'), 
       (2, 20, CAST(10.000000 AS Decimal(18, 6)), N'StackDesc'),
        3, 10, CAST(10.000000 AS Decimal(18, 6)), N'Descrip2')
GO

So basically I need to group those columns by product id and sum their total quantity. And I can do that by a simple query such as

SELECT 
    MAX(ID) AS LastID, Product_Id, SUM(Quantity) AS Quantity 
FROM 
    stackquestion
GROUP BY 
    Product_Id

The point is how to get total description of all items in that group. My description column needs to contain all descriptions in that group (SUM of nvarchar)

Output should be something like this:

Answer 1

If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below

SELECT 
    MAX(ID) AS LastID, Product_Id, SUM(Quantity) AS 
    Quantity,string_agg(concat( [Description]), CHAR(13)) AS Description

FROM 
    stackquestion
GROUP BY 
    Product_Id

Answer 2

According to @Andomar the one of solutions would be query like STUFF

SELECT 
    MAX(ID) AS LastID, Product_Id, SUM(Quantity) AS Quantity,
    Description = STUFF((SELECT ',' + [Description] 
                         FROM stackquestion c 
                         WHERE c.Product_Id = stackquestion.Product_Id 
                         FOR XML PATH('')), 1, 1, '')
FROM 
    stackquestion
GROUP BY
    Product_Id

Answer 3

More than you want but has the data you want

SELECT ID, Product_Id, Quantity
     , SUM(Quantity) over (partition by Product_Id) AS SumQuantity 
FROM stackquestion
ORDER BY 
    Product_Id, ID