Menu
I have a table (EMP) I know that using the COALESCE function we can get the values of any column in this way
23,23,45,34 SELECT OfferID FROM Emp where EmpID= 23 but I am not getting the syntax to achieve this
Any help would be great, to resolve this issue.
742
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
The SQL COALESCE function can be syntactically represented using the CASE expression. For example, as we know, the Coalesce function returns the first non-NULL values. SELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME; The above Coalesce SQL statement can be rewritten using the CASE statement.
DECLARE @List VARCHAR(8000) SELECT @List = COALESCE(@List + ',', '') + CAST(OfferID AS VARCHAR) FROM Emp WHERE EmpID = 23 SELECT @List This approach to aggregate concatenation is not guaranteed to work. If you are on at least SQL Server 2005 XML PATH or CLR aggregates are preferred.
The definitive article on the subject is Concatenating Row Values in Transact-SQL
171
I have done this using COALESCE in the past too, but i suggest another approach because you dont need a variable. Use the T-SQL function STUFF to get this done.
SELECT STUFF(( select ','+ cast(OfferID as nvarchar(255)) from Emp b WHERE a.EmpID= b.EmpID FOR XML PATH('') ),1,1,'') AS COLUMN2 FROM Emp a GROUP BY a.EmpID STUFF (Transact-SQL)
20
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
