Making or creating comma separated value list in a single line using SQL SP or UDF

Please visit my new Web Site https://coderstechzone.com

Most of the times we need to display a summary or quotation line in our page by making a comma separated value list from SQL Server table data rows. But the reality is we did not get any built in function from SQL Server. But we did it by applying few techniques. Lets for SQL Server we can use the COALESCE function, In SQL Server 2000 we can use a variable smartly in our SELECT statement. To do that here i shows technique for both SQL Server 2005 and 2000 to make a single line comma separated value from multiple rows based on a condition. Lets we have a requirement like the "XYZ" company will provide seasonal services. So that the company wants to put the all today's available services in the top level of company home page. In this scenario you can easily identify the today's services by a simple select statement with GETDATE() condition. Then you can loop through the rows and run time make a single line string to meet the requirement. But this is not the best solution as well as complex. Lets look the smart way:

Fig: Data structure

SQL Server 2005:

DECLARE @Services VARCHAR(MAX)

SELECT @Services = COALESCE(@Services+', ' , '') + [Name]
FROM Service
WHERE GETDATE() BETWEEN StartDate AND EndDate

SELECT @Services Services

Output: Free home TV service, Home delivery of giveaway

SQL Server 2000:

DECLARE @Services VARCHAR(8000)

SET @Services = ''

SELECT @Services = @Services + [Name] + ','
FROM [Service]
WHERE GETDATE() BETWEEN StartDate AND EndDate

SELECT SUBSTRING(@Services , 1, LEN(@Services)-1)

Output: Free home TV service, Home delivery of giveaway

TIPS:
1. Use ISNULL() to handle NULL values or filter all null values & spaces within the WHERE clause.
2. To resolve the ERROR: Invalid length parameter passed to the SUBSTRING function use case in the select statement to check the length before applying SUBSTR.
3. You can use the above technique to create your user defined function(UDF) or stored procedure(SP).
4. You can also use the above technique to create a dynamic int id list to use in IN clause within the sub query or inline query.