Concatenating a column within the same column in SQL Server

Question

Hi so I have a question on concatenating a column. I have two tables.

Table 1: Sales Order-> OrderID, ProductsOnHold (Should list product(s) on hold)
Table 2: Products-> ProductID, OnHold (boolean)

A Sales Order can have many products.
A product can have a hold, which would make the Sales Order be put on hold if that order has that product.
So a sale order can be on hold for have 1 or more products on hold.

I was able to get one product to display if the order is on hold, but what if it has more products on hold - how could I display them as well? This is for a view I am creating.

This is what I have done so far:

(SELECT ProductName
 FROM Products with (NOLOCK)
 WHERE (OnHold = 1) AND (EXISTS   
    (SELECT CASE
               WHEN (COUNT(DISTINCT Product)> 1) THEN (Product + ', ' + Product)
               ELSE Product END AS ProductName
     FROM  SalesOrder WITH (NOLOCK) 
     GROUP BY OrderID ))) AS ProductsOnHold

Desired Output:

OrderID | ProductsOnHold
----------------------------------
    1   | P1, P2, P7                      
    2   |                
    3   | P1            
    4   | P1, P7, P8, P9, P15, P77

Anything I am missing in my sql query?

Basically, you want an equivalent of the MySQL function called `GROUP_CONCAT`. There's a question with many answers about that: [Simulating group_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005). — Andriy M, Jun 05 '13 at 17:23

score 0 · Answer 1 · answered Jun 05 '13 at 18:01

My solution [I am not sure if I understand your problem well]

user function

create function inonerow (@orderid int)
returns nvarchar(max)
begin
declare @data nvarchar(max)
SELECT @data =coalesce(@data + ',','')+ CONVERT(varchar,prodid) from SalesOrder 
inner join product on product.prodid = salesorder.prodid where orderid=@orderid and product.oshold = 1
return @data
end

select distinct orderid, dbo.inonerow(orderid) from salesorder
inner join product on product.prodid = salesorder.prodid  
where product.onhold = 1

Hope this works :)

user2065377 · Accepted Answer · 2017-09-01T18:32:31.973

Assuming that you have 2 tables SalesOrder and Product with ProductID as the "relationship" column, you can join those 2 tables and then transpose using a "for xml" clause.

    select s1.OrderID,
stuff(
        (select ','+p.ProductName as [text()]
        from SalesOrder s
            join Product p on p.ProductID = s.ProductID and p.OnHold = true
        where s.OrderID = s1.OrderID
        group by p.ProductName 
        for xml path (''))
    ,1,1,'') as ProductsOnHold from SalesOrder s1 group by s1.OrderID

Hope this helps.

Concatenating a column within the same column in SQL Server

2 Answers2

Linked