Add two nvarchar columns using T-SQL

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:

The solution for SQL Server 2008 involves `stuff` and `for xml`. See for example https://stackoverflow.com/a/17591536/50552, or Google for "SQL Server group_concat". — Andomar, Apr 01 '18 at 10:04
I can probably find more duplicates if this isn't enough :-) — Zohar Peled, Apr 04 '18 at 12:16

score 2 · Accepted Answer · answered Apr 01 '18 at 14:12

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

score 0 · Answer 2 · edited Apr 01 '18 at 11:51

0

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

edited Apr 01 '18 at 11:51

marc_s

732,580
175
1,330
1,459

answered Apr 01 '18 at 10:12

PyDeveloper

309
6
24

2

I'd like to add this is the go-to kind of solution prior to SQL Server 2017 which finally saw the rise of STRING_AGG function: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql – MK_ Apr 01 '18 at 10:28

paparazzo · Answer 3 · 2018-04-01T20:14:40.417

0

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

edited Apr 01 '18 at 20:14

answered Apr 01 '18 at 13:50

paparazzo

44,497
23
105
176

Typo in ORDER.. Thanks – PyDeveloper Apr 01 '18 at 20:08

Add two nvarchar columns using T-SQL

3 Answers3