April 6, 2006 at 1:28 pm
Hi all.
I saw this done in a Script O' The Day, but I can't find it. Basically I need to store the concantenated results of a given column (in several rows) into a variable.
I know it can be done, but I can not find the script again.
Help!
Thanks!
Jeff
April 6, 2006 at 1:42 pm
DECLARE @string VARCHAR(8000)
SET @string =''
SELECT @string = @string + <ColumnName> FROM <TableName>
Amit Lohia
April 6, 2006 at 2:00 pm
Amit,
Thank you for your reply! I didn't explain myself well enough in the original post though. For example, I have 6 rows that I want to concantenate into one variable:
[UnNamed Batch].............................................: 03/22/2000|
ADOT Compliance Notification Letter..........................: 08/22/2001|
Service Increase Confirmation Apr 2003.....................: 04/01/2003|
Service Increase Confirmation - Apr Cycle 1................: 03/25/2004|
ISSUE 51 Newsletter.........................................: 01/18/2005|
WRun05_V9A_D5 Customer TriFold.........................: 03/01/2005|
I want to select all of these cells as a string into the same variable. Any ideas?
Jeff
April 6, 2006 at 2:32 pm
the above script will do it
Just add "|"
like @String + "|" + <ColumnName>"
Amit Lohia
April 6, 2006 at 3:43 pm
That worked. Thanks for your help, Amit!
Jeff
April 7, 2006 at 8:43 am
Amit's solution adds a delimiter before the first value. Using the dates in the original example, his code will return this:
|03/22/2000|08/22/2001|04/01/2003|03/25/2004|01/18/2005|03/01/2005
By making a quick tweak, you can remove the first delimiter and return this:
03/22/2000|08/22/2001|04/01/2003|03/25/2004|01/18/2005|03/01/2005
Here is Amit's code with the tweak in place (notice that the " SET @string ='' " line of code has been removed):
DECLARE @string VARCHAR(8000)
SELECT @string = COALESCE(@string + '|' , '') + <ColumnName> FROM <TableName>
This works because the @string is NULL for the first value. Because it is null, @string + '|' will return NULL, and the Coalesce function will return an empty string. When you concatenate the empty string with the value from the field you get just the value from the field. With the next record, @string will no longer be NULL, so you will then start getting the delimiters appended.
Also, no matter which of these ways that you choose, you should add a where clause to your query to make sure that you don't get any nulls in <ColumnName>, otherwise you won't get what you expect.
FYI: The Coalesce function works sort of like the IsNULL function, except that with the IsNULL function you pass a test value (usually a field) and a value to use if the test value is NULL. With the Coalesce function, you can pass it as many items as you want and it will return the first one it finds that is Not NULL.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply