[SOLVED] How to concatenate two column data into one?

How to concatenate two column data into one?

I want to concatenate the data in botanical column into item column. The code below work perfectly in concatenating location column, but l don't know how to apply concatenation for multiple column data.

farmtable

Id	Item	Botanical Name	Location
1	Apple	Malus spp	Block C
2	Coffee	Coffea	Block A
3	Coffee	Coffea	Block B
4	Apple	Malus spp	Block B

SELECT
 Item,
 Botanical,
 STUFF(
     (SELECT DISTINCT ', ' + Location
      FROM farmtable
      WHERE Item = a.Item
      FOR XML PATH (''))
      , 1, 1, '')  AS URLList
FROM farmtable AS a
GROUP BY Item, Botanical

Expected Result

Id	Item	Location
1	Apple, Malus spp	Block C, Block B
2	Coffee, Coffea	Block A, Block B

Solution

You can update your code and use CONCAT(Item, ' ', Botanical).

In your query, update SELECT by removing Item, Botanical and use CONCAT(Item, ' ', Botanical).
As mentioned in one of the comment, you can use STRING_AGG.

Here is the complete query.

SELECT
 CONCAT(Item, ' ', Botanical) AS Item,
 STRING_AGG(Location, ',') AS [Location]
FROM farmtable AS a
GROUP BY Item, Botanical

In case you want to stick with STUFF then use CONCAT as mentioned above & update WHERE condition inside STUFF to Item = a.Item AND Botanical = a.Botanical.

Here is the complete query.

SELECT
 CONCAT(Item, ' ', Botanical) AS Item,
 STUFF(
     (SELECT DISTINCT ', ' + Location
      FROM farmtable
      WHERE Item = a.Item AND Botanical = a.Botanical
      FOR XML PATH (''))
      , 1, 1, '')  AS [Location]
FROM farmtable AS a
GROUP BY Item, Botanical