Build an Array of items depending of column

Question

I have a query like this:

SELECT 
    MCU.UserName
    ,MCU.McUserId AS UserId
    ,O.OrderId
    ,PD.ProductId
    ,O.ShippingId
    ,PD.[Name] AS ProductName
    ,O.OrderDate
    ,O.Total
    ,O.Subtotal
    ,O.PaymentStatus
    ,O.OrderNumber
    ,O.[Type]
    ,O.CreatedBy
    ,O.ModifiedBy
     FROM SecurityMc.McUsers AS MCU
     INNER JOIN Store.[Order] AS O ON  MCU.McUserId = O.UserId
     INNER JOIN Store.OrderItem AS OI ON O.OrderId = OI.OrderId
     INNER JOIN Customer.Shipping AS CS ON O.ShippingId = CS.ShippingId
     INNER JOIN Product.ProductDetail AS PD ON OI.ProductId = PD.ProductId
     WHERE  O.[Type] = 'Order' AND O.IsDeleted = 0 AND 1 = LanguageId

It returns data like:

+--------+---------+-----------+-------------+
| UserId | OrderId | ProductId | ProductName |
+--------+---------+-----------+-------------+
|      1 |       3 |         1 | Dress       |
|      1 |       3 |         2 | Boots       |
|      1 |       3 |         3 | Socks       |
|      1 |       3 |         4 | Extension   |
+--------+---------+-----------+-------------+

As you can see it returns one ProductName for each row, but I want to send it in just one array, as you can see it have same OrderId and UserId, its possible to concat ProductName if OrderId is the same and get something like:

ProductName(1 column): Dress,Boots,Socks,Extension

How can I achieve it? Regards

You could easily find a plethora of answers on this subject by searching for something like "concatenate rows sql server" (this is most definitely a duplicate question). Apart from that, it looks like you're trying to send something to some other software? Your result isn't going to be an 'array' per se, just a string of comma-separated values. — ZLK, Apr 27 '18 at 01:48

lmarqs · Answer 1 · 2018-04-27T02:53:54.503

0

I think that what are you looking for is the STRING_AGG function. Please, check this fiddle.

Also you can use a recursive query + window functions

edited Apr 27 '18 at 02:53

answered Apr 27 '18 at 01:54

lmarqs

1,469
13
16

Thats exactly what I want, but is not supported by SQL 2016 – David Apr 27 '18 at 01:58
It's not possible to doin sql 2016? – David Apr 27 '18 at 02:13
@Davi, I've updated my answer adding another possible solution. (recursive query + window functions) – lmarqs Apr 27 '18 at 02:55
Can you explain me what is MaxRn and Rn columns? – David Apr 27 '18 at 03:03
@David Sure thing. For each product that belongs to the same order is created a sequential number (http://sqlfiddle.com/#!18/25ea2/1). After that, the max sequential number is calculated. (http://sqlfiddle.com/#!18/25ea2/2). The recursive query concatenates the values row by row (like a loop) and `ms.rn = ms.maxRn` filters the last concatenation (http://sqlfiddle.com/#!18/25ea2/3) – lmarqs Apr 27 '18 at 03:25

Build an Array of items depending of column

1 Answers1