I am trying to concatenate many columns and separating it with a comma as below:
Column
------
abc
bcd
bgd
abc
Expected output: abc,bcd,bgd
I am using this code:
CREATE FUNCTION concatinate(@PK uniqueidentifier)
RETURNS varchar(max)
AS
BEGIN
DECLARE @result varchar(max)
SELECT @result = ISNULL(@result + ', ', '') + Column
FROM table
The result I am getting is
abc,bcd,bgd,abc
I am not able to only select the distinct values. Please guide.
SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table; Output: Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(' ') in CONCAT() function. This method will change the original table.
In SQL, you can also concatenate numerical data from the table in the same way as we concatenate strings. The CONCAT function can also be used to join numeric values.
Suppose your table name is tb then your sql query would be:
SELECT dist.Column + ','
FROM(
SELECT DISTINCT t.Column
FROM dbo.tb t) dist
FOR XML PATH ('')
Using this approach you will get unique values. But at the end you will get an extra comma ','. This can removed by using string helper functions. Hope this helps
A few amendments to your original code:
DECLARE @result varchar(max)
SELECT @result = ISNULL(@result + ', ', '') + dist.Column
FROM (SELECT DISTINCT Column FROM table) dist
PRINT(@result)
This will concatenate a distinct list. Unlike the initial answer above, you will not get an extra comma ',' at the end.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With