SQL multiple results in one cell

Question

I have a database in which an invoice can be linked to multiple orders. I'd like to have multiple results from the order table put in one cell, but (And that's the problem) I need to have the orders filtered the same way as they are from the main query, without rewriting them in the subquery. I doubt it is possible, but the idea is actually to rely as much as possible on the main query, mainly because the columns are created automatically and independently of the conditions.

The query:

SELECT [Invoice].INV_ID AS 'Invoice ID',
       INV_TIT AS 'Invoice title', 
       (SELECT CAST((SELECT ORD_TIT + ' | ' FROM ORD WHERE ORD_ID IN ([Order].ORD_ID) FOR XML PATH('')) AS VARCHAR(MAX))) AS 'Order list'
FROM INV [Invoice]
JOIN INV_ORD [InvoiceOrder] ON [Invoice].INV_ID = [InvoiceOrder].INV_ID 
INNER JOIN ORD [Order] ON [InvoiceOrder].ORD_ID = [Order].ORD_ID
WHERE [Invoice].INV_ID IN (29517, 30951, 42048)
  AND [Invoice].INV_ISDEL = 0
  AND [Order].ORD_DAT = GETDATE()

The result:

╔════════════╦════════════════╦═══════════════════╗
║ Invoice ID ║ Invoice title  ║ Order list        ║
╠════════════╬════════════════╬═══════════════════╣
║ 1          ║ Cinema tickets ║ 1 cinema ticket   ║
║ 1          ║ Cinema tickets ║ 2 cinema tickets  ║
║ 2          ║ Groceries      ║ 1 toothbrush      ║
║ 2          ║ Groceries      ║ 5 shampoo bottles ║
╚════════════╩════════════════╩═══════════════════╝

The desired result:

╔════════════╦════════════════╦════════════════════════════════════╗
║ Invoice ID ║ Invoice title  ║ Order list                         ║
╠════════════╬════════════════╬════════════════════════════════════╣
║ 1          ║ Cinema tickets ║ 1 cinema ticket | 2 cinema tickets ║
║ 2          ║ Groceries      ║ 1 toothbrush | 5 shampoo bottles   ║
╚════════════╩════════════════╩════════════════════════════════════╝

Concatenate across rows is a popular question on SO. Microsoft should have added this function to SQL Server a long time ago. — Code Different, Aug 12 '15 at 15:29
It's not a duplicate, since the other question uses no WHERE clause :( — Valentin Manceaux-Panot, Aug 12 '15 at 15:31
http://stackoverflow.com/questions/31351437/concatenate-values-of-one-column-to-single-text has two good answers to this question as well. — APH, Aug 12 '15 at 17:46
You can add the `WHERE` clause to the solution that @ZoffDino linked to. — PicoDeGallo, Aug 12 '15 at 17:55

score 2 · Accepted Answer · answered Aug 13 '15 at 05:24

try:

create table #tmp (Id int,value1 varchar(max),value2 varchar(max))

insert into #tmp values
(1,'Cinema tickets','1 cinema ticket'),
(1,'Cinema tickets','2 cinema tickets'),
(2,'Groceries','1 toothbrush '),
(2,'Groceries','5 shampoo bottles')


SELECT Id,value1,STUFF(
             (SELECT '|' + value2 
              FROM #tmp where Id=t.Id
              FOR XML PATH (''))
             , 1, 1, '') from #tmp t group by Id,value1

SQL multiple results in one cell

1 Answers1