Menu
I need a similar function to Oracle WM_CONCAT in SQL Server, which returns a comma separated list of whatever field you pass it as argument. For example, in Oracle,
select WM_CONCAT(first_name) from employee where state='CA'
returns "John, Jim, Bob".
How can I do this in SQL Server?
Thanks
592
In the output of SQL Server Concatenate using SQL Plus (+) operator, we have concatenate data from these fields (firstname, MiddleName and LastName) as a new column FullName. We have a drawback in SQL Server Concatenate data with SQL Plus(+) operator. Look at the following example.
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
Concatenates two strings and sets the string to the result of the operation. For example, if a variable @x equals 'Adventure', then @x += 'Works' takes the original value of @x, adds 'Works' to the string, and sets @x to that new value 'AdventureWorks'.
Another way to implement Concat in SQL with the numerical value is to use the CAST operator. This operator converts the numerical data into the string format. Using the + (plus) operator will manipulate the numeric data into string concatenation.
In SQL Server 2017 STRING_AGG function has been added
SELECT t.name as TableName
,STRING_AGG(c.name, ';') AS FieldList
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
GROUP BY t.name;
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
