July 23, 2002 at 8:27 am
hi, I remember there is an undocumented select usage which select all columns of one record into a single string, but I forgot the exact usage, anybody know this?
July 23, 2002 at 4:36 pm
None I know of. Be intersted to see if there is.
Steve Jones
July 23, 2002 at 7:06 pm
Sorry, should be a trick that build a string of one column from all record, like this:
declare @s-2 varchar(4000)
select @s-2 = 'start from here:'
select @s-2 = @s-2 + <some_col_name> from <some_table_name>
However, I need a function that accept table name and primary key, and return the string of all columns values delimited by specified delimiter. Since the function is not available from ms sql, I write one by myself. If you are interested, I would like to mail one to you.
July 23, 2002 at 7:06 pm
Sorry, should be a trick that build a string of one column from all record, like this:
declare @s-2 varchar(4000)
select @s-2 = 'start from here:'
select @s-2 = @s-2 + <some_col_name> from <some_table_name>
However, I need a function that accept table name and primary key, and return the string of all columns values delimited by specified delimiter. Since the function is not available from ms sql, I write one by myself. If you are interested, I would like to mail one to you.
July 24, 2002 at 1:55 am
This is a short piece of T-SQL I read here some time ago. I tried to reproduce it quickly from memory. It is not perfect, but it might set you on the right track...
--Create the table that hold the values
create table tbl_concatenate
(pk int identity primary key,
value varchar(10))
--Retrieve the concatenation of 'value'
declare @result varchar(4000)
set @result = '''Start : '''
select @result=@result+'-'+t.value
from tbl_concatenate t order by pk
--Output the result
print @result
You should do some specific handling for the first record that is retrieved, and you have to be carefull with concatenating null values (hence the initial value for @result).
HIH
September 20, 2002 at 8:35 am
A note on NULL's
NPeeters above commented on concatenating null values.
When building strings on the fly in a select statement, a null value in any one of the returned fields will convert your entire string to a NULL value.
Here are two ways to get past that.
The first is simple, just place this set code in your stored procedure or batch segment:
SET CONCAT_NULL_YIELDS_NULL OFF
The second method looks more impressive and is useful if you want to convert a NULL value into a new value on the fly with in your Select into string:
This function is; COALESCE and it works like this: (example using Northwind)
SELECT COALESCE (Region, 'UNASSIGNED') AS Region
FROM Employees
So the final Result using Northwind again (Output in HTML):
Declare @HTMLOutput Varchar (8000)
Set @HTMLOutput = '<TABLE Border="1"><TR><TD>Employee ID</TD><TD>Last Name</TD><TD>First Name</TD><TD>Title</TD><TD>Region</TD></TR>'
Select @HTMLOutput = @HTMLOutput + ('<TR><TD>' + COALESCE (CAST(EmployeeID as varchar), 'Record Error') +
'</TD><TD>' + COALESCE (LastName, 'Unknown') + '</TD><TD>' +
'</TD><TD>' + COALESCE (FirstName, 'Call him BOB') + '</TD><TD>' +
'</TD><TD>' + COALESCE (Region, 'UNASSIGNED!') + '</TD></TR>') from Employees
Set @HTMLOutput = @HTMLOutput + '</Table>'
Select @HTMLOutput as HTML
So there you go, any NULL value on the left side in the COALESCE will be replaced with the text on the right side. (Remember to cast non string types to Varchar)
Hope this is helpful
--> David V.F. Burton <--
--> David V.F. Burton <--
http://www.siantrex.net
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply