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 can do it by applying few techniques. Lets for SQL Server 2005, 2008 & 2012 we can use the COALESCE function, In SQL Server 2000 we can use a variable smartly in our SELECT statement. To do that I will discuss all techniques for both SQL Server 2000 and 2005 with all upper versions 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 and a bit complex also. Lets look at the smart way:
SQL Server 2005 & Upper Versions:
DECLARE @Services VARCHAR(MAX) SELECT @Services = COALESCE(@Services+', ' , '') + [Name] FROM Service WHERE GETDATE() BETWEEN StartDate AND EndDate SELECT @Services Services
Output: Like: 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)
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.