Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jamesbeal
Starting Member
5 Posts |
 Posted - 2011-09-24 : 09:57:08
|
| Hi,I'm a total newbie to anything advanced in SQL all I've done so far are selects and updates etc so apologies for any wrong terminology but here's what I need.I have a table with 20 columns in and I'd like to select a record but return them all in 1 field with a comma between each value?? EG Table has ColumnsColumn1 Column2 Column3123 456 789I'd like to return a value of "123,456,789" is this possible?J Beal |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 10:07:07
|
| [code]SELECT Column1 + ',' + Column2 + ',' + Column3[/code]Each column will have to already be Char / Varchar datatype, so if this is not the case then you will need[code]CONVERT(varchar(20), Column1) + ',' + ...[/code]varchar(20) is wide enough for Numbers, if you have Dates or GUIDs etc. you may need some formatting and/or wider varchar sizeIf one of the columns is NULL then the result will be NULL, so use:[code]COALESCE(Column1, '') + ','[/code]to convert NULLs to blank strings.If you want to leave out the comma when a column is NULL (e.g. for addresses so your don't get "1 High Street,,,,Anytown" then use:[code]COALESCE(Column1 + ',', '') + COALESCE(Column2 + ',', '') + ...[/code]If converting from number etc put the CONVERT inside the COALESCE:[code]COALESCE(CONVERT(varchar(20), Column1), '') ...[/code] |
 |
|
|
jamesbeal
Starting Member
5 Posts |
Posted - 2011-09-24 : 10:08:58
|
| Thanks your a star!J Beal |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 10:11:01
|
| Sorry, some typos which I have fixed since you replied. |
|
|
|
|
|
|
|
|
Returning Multiple Columns into 1 field