Query Results with commas b/w fields

[asantavicca]

Ok, so I want to execute this query:

Select user_id, f_name, l_name
from tableABC;

And I want it to return in this format:

abc123, Joe, Smith
sid938, Mel, Ward
iic837, Al, Jamison

I want the returned fields to be separated by commas. How would I modify the sql query to make this happen? I've seen it done elsewhere, but I'm not a dba guy and thought someone here might be able to quickly tell me how to do this.

Not sure if it matters, but this is on sql server.

Thanks!

[Krzemo]

I want the returned fields to be separated by commas.

I'm not sure what UR trying to do. Where should be executed this query? In SQL explorer (or in any other "client SQL tools")? In program that U write?
The solution depends on that!

If its SQL explorer than U have to select "generate results in text" and choose "comma separated" as a format specification.

OR U can also do it the hard way by concatenating strings like that:

Select IsNull(user_id,'')+','+IsNull(f_name,'')+','+IsNull(l_name,'')
from tableABC

Best regards,
Krzemo

[asantavicca]

Eh, that didn't work.

It's a proprietary tool that comes with the application that allows us to access the database without needing to log into the db server. It sits on the app server. I just drop a file with the sql statement into the tool and it executes the sql statement for me. It's just plain old sql.

[dee-u]

Did you try the code posted by Krzemo?

Select IsNull(user_id,'')+','+IsNull(f_name,'')+','+IsNull(l_name,'')
from tableABC

[asantavicca]

Did you try the code posted by Krzemo?

Heh...that's what I was talking about. It didn't seem to work.

[dee-u]

From the looks of it it should work, what did you mean by it didn't seem to work? No result were returned or you encountered some error?

[asantavicca]

Returned an error

Succesfully connected to Microsoft SQL Server.
Time: Thu Jan 08 15:53:07.388 2009

[Select IsNull(user_name,'')+','+IsNull(full_name,'')+','+IsNull(login_time,'') from ip.ip_user]
ERROR: Unable to perform SQL [Select IsNull(user_name,'')+','+IsNull(full_name,'')+','+IsNull(login_time,'') from ip.ip_user].

Processed 2 lines and 0 SQL statements.
Time: Thu Jan 08 15:53:11.701 2009, Elapsed: 4.313 seconds
Done processing.

Seems like it's not working to me. Any other thoughts? I wonder if this proprietary tool can't handle stuff like this....

[dee-u]

That could be a problem of that tool you are using, the sql looks fine to me.

[asantavicca]

Yeah, that's what it looks like. Thanks for the suggestion, though.

[ComITSolutions]

In SQL Server 2000

Select Cast(IsNull(user_Id,0) as Varchar)+','+IsNull(user_name,'')+','+IsNull(full_name,'')+','+
Cast(IsNull(login_time,'12am') as Varchar) from ip.ip_user

For datetime type of data you can also use convert function to get date in specific format.

In your statement IsNull(login_time,'') is culprit when login_time is null you can not substitute with string null the substitute value has to be same datatype