John shares with us a few queries he uses, and would like to pass on to our readers.
John writes:
Concerning your scripts for creating a comma separated list from a single field:
This is what I do to perform the same functionality. It is a bit more robust I think since I am also formatting the data a bit, but others may disagree. Please see the notes as there are some substitutions that need to be made for the driving queries. There is almost more documentation than code below (as it should be, IMHO). I hope this helps someone!
/*
Notes: 1. Change "fieldname" to the appropriate field name.
2. Change "tablename" to the appropriate table name.
3. There are two select statements below, one for selecting
all values in a field and one for selecting distinct values (which is uncommented as default).
*/
-- declare variable
DECLARE @MyList NVARCHAR(MAX)
/*-- full listing - trimming returned values to prevent massive padding
SET @MyList = ''
SELECT @MyList = ISNULL(@MyList,'') + LTRIM(RTRIM(fieldname)) + ',' FROM tablename;
SELECT @MyList
--*/
-- SELECT * FROM ldr_variables
--/*-- distinct listing - trimming returned values to prevent massive padding
SELECT @MyList =ISNULL(@MyList,'')+ LTRIM(RTRIM(fieldname))+ ', '
FROM (SELECTDISTINCT fieldname FROMtablename) x;
--*/
-- trim the last delimiter off
SET @MyList =SUBSTRING(@MyList,1, LEN(@MyList)-1)
--/* this is optional - I usually have it turned off, but I needed it for a specific purpose and I'm just passing it on
-- check for leading delimiter and spaces in case there was a space or double quotes
---- NOTE: this will only catch one instance of a space, space-comma, or comma
SELECT @MyList =CASE
-- check double combinations first
WHENSUBSTRING(@MyList,1, 1)= ' ,'
THENSUBSTRING(@MyList,3, LEN(@MyList))
WHENSUBSTRING(@MyList,1, 1)= ', '
THENSUBSTRING(@MyList,3, LEN(@MyList))
WHENSUBSTRING(@MyList,1, 1)= ' '
THENSUBSTRING(@MyList,2, LEN(@MyList))
-- now check single combinations
WHENSUBSTRING(@MyList,1, 1)= ' ' -- this should not happen with TRIMs above
THENSUBSTRING(@MyList,2, LEN(@MyList))
WHENSUBSTRING(@MyList,1, 1)= ','-- this should not happen unless the data can have it as a value
THENSUBSTRING(@MyList,2, LEN(@MyList))
ELSESUBSTRING(@MyList,1, LEN(@MyList))
END
--*/
SELECT @MyList;
If you have any questions or comments, please feel free to contact me at JMiceli@wrberkley.com.
Understand that this is an open forum. Feel free to use any of the code found here at your own risk.
Thanks John for sharing freely.
Cheers,
Ben