TAG | TSQL
I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database. One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list. Here is the SQL 2005/2008 code that I just created for use in a client environment:
CREATE FUNCTION [dbo].[fn_LinkedString] ( -- Add the parameters for the function here @ID varchar(10) ) RETURNS nvarchar(1000) AS BEGIN -- Declare the return variable here DECLARE @Result nvarchar(1000) -- Add the T-SQL statements to compute the return value here SELECT @Result= x.List FROM ( SELECT L1.ID, List = SUBSTRING((SELECT (', '+ LinkedID) FROM Linked L2 WHERE L1.ID = L2.ID ORDER BY ID, LinkedID FOR XML PATH('') ), 3, 1000) FROM Linked L1 GROUP BY ID ) x WHERE x.ID = @ID -- Return the result of the function RETURN @Result END
I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.
I occasionally need to get some representative values from a table for testing and thought my solution might be something worth sharing. I tend to think in nested selects and go back and clean up code into joins where appropriate. Below you will see that I am using a group by and a min function to identify single instances of different types, and then pulling the required data in an outer select:
SELECT UserName FROM Users WHERE UserID in ( SELECT MIN(UserID) FROM USERS WHERE Active = 1 GROUP BY TypeCode )