Sql For XML Path About

Question

Hi,

I prepared sample tables. I also stated the result. What I want to do is show multiple lines in OrderItem in a single line.

http://sqlfiddle.com/#!18/43b5f9/1

| ID | CustomerID |              CreatedDate | Adress |
|----|------------|--------------------------|--------|
|  1 |          1 | 2021-06-20T11:45:52.113Z | Deneme |


| ID | ProductID | Price | Quantity | OrderId |
|----|-----------|-------|----------|---------|
|  1 |         1 |     2 |        1 |       1 |
|  2 |         2 |     5 |        2 |       1 |


| ID |           Name |
|----|----------------|
|  1 |   Product Name |
|  2 | Product Name 2 |


| ID |   Gtin | ProductId | AttributeId |
|----|--------|-----------|-------------|
|  1 | 123456 |         1 |          10 |
|  2 | 563245 |         2 |          20 |

| ID | ProductCombinationId | Size |
|----|----------------------|------|
|  1 |                   10 |   XL |
|  2 |                   20 |    L |

| ID |                           Products |
|----|------------------------------------|
|  1 | ProductName Size : XL Quantity : 1 |

Answer 1

Hi @Ali Yılmaz ,

Welcome to the microsoft TSQL Q&A forum!

Because the OrderId corresponding to ID=1 and ID=2 are both 1, redundant results will be returned. A simple modification based on the ErlandSommarskog code can return the answer you want:

SELECT O.ID, substring(orderdata, 1, len(orderdata) - 2)  
 FROM   [Order] O  
 CROSS  APPLY (SELECT concat(P.Name, ' Size: ',  PS.Size, ' Quantity: ', oi.Quantity)  
               FROM   OrderItem oi  
               JOIN   Product P ON oi.ProductID = P.ID  
               JOIN   ProductCombination PC ON oi.ProductID = PC.ProductId  
               JOIN   ProductSize PS ON PC.AttributeId = PS.ProductCombinationId  
               WHERE  oi.Id = 1  
               FOR XML PATH('')) AS od(orderdata)  

If you have any question, please feel free to let me know.

Regards
Echo

---

Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

On SQL 2017 and later:

SELECT oi.OrderId, string_agg(concat(P.Name, ' Size: ',  PS.Size, ' Quantity: ', oi.Quantity), ' | ')
FROM   OrderItem oi
JOIN   Product P ON oi.ProductID = P.ID
JOIN   ProductCombination PC ON oi.ProductID = PC.ProductId
JOIN   ProductSize PS ON PC.AttributeId = PS.ProductCombinationId
GROUP  BY oi.OrderId

On SQL 2016 and earlier:

SELECT O.ID, substring(orderdata, 1, len(orderdata) - 2)
FROM   [Order] O
CROSS  APPLY (SELECT concat(P.Name, ' Size: ',  PS.Size, ' Quantity: ', oi.Quantity, ' | ')
              FROM   OrderItem oi
              JOIN   Product P ON oi.ProductID = P.ID
              JOIN   ProductCombination PC ON oi.ProductID = PC.ProductId
              JOIN   ProductSize PS ON PC.AttributeId = PS.ProductCombinationId
              WHERE  oi.OrderId = O.ID
              FOR XML PATH('')) AS od(orderdata)

Answer 3

Hi,

When I run the code below, it works as well as the Order table. It brings all sizes of just one product. My aim is to bring only those products in the specified Order.

			  SELECT O.ID, substring(orderdata, 1, len(orderdata) - 2)
FROM   [Order] O
CROSS  APPLY (SELECT concat(P.Name, ' Size: ',  PS.Size, ' Quantity: ', oi.Quantity, ' | ')
              FROM   OrderItem oi
              JOIN   Product P ON oi.ProductID = P.ID
              JOIN   ProductCombination PC ON oi.ProductID = PC.ProductId
              JOIN   ProductSize PS ON PC.AttributeId = PS.ProductCombinationId
              WHERE  oi.OrderId = 'I give OrderID'
              FOR XML PATH('')) AS od(orderdata)