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 |
|
blessy
Starting Member
6 Posts |
 Posted - 2011-09-21 : 00:59:47
|
| Hi, I have a table named interest, which stores multiple interests of a person. I am displaying all such interest on a form using CheckeBoxList in asp.net. I want to store the checked/selected items to another table Description. How can i store multiple values into a single column.Blessy K John |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-09-21 : 01:39:57
|
| Not quite sure if I'm following, but perhaps something like this?[CODE]declare @interest table (id int identity not null, ColA varchar(10) null, ColB varchar(10) null, ColC varchar(10) null)insert into @interest (ColA, ColB, ColC)values (null, null, '1.1%'),('3.2%', '1.1%', '2.0%')declare @description table (id int identity not null, NewColumn varchar(30))insert into @description (NewColumn)(select coalesce(ColA,'0.0%')+', '+coalesce(ColB, '0.0%')+', '+coalesce(ColC,'0.0%') from @interest)select * from @description(2 row(s) affected)(2 row(s) affected)id NewColumn----------- ------------------------------1 0.0%, 0.0%, 1.1%2 3.2%, 1.1%, 2.0%(2 row(s) affected)[/CODE] |
 |
|
|
blessy
Starting Member
6 Posts |
Posted - 2011-09-21 : 02:26:16
|
| it worked. Thanks a lot. But i couldn't understand what 'coalesce' is.Blessy K John |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-09-21 : 02:45:15
|
| I just added that in case you can have null values in those columns. If you can have nulls, then you have to have a way to handle them in order to concatenate the multiple columns because you can't add a null value to another value (I think I explained that right).So all the coalesce is doing is saying:If the value of the column is null, replace the null value with '0.0%' Basically, it's just there to ensure that you don't screen out columns because of a null. In my example above, if I didn't use coalesce or another null-handling function, I'd end up with:[CODE](2 row(s) affected)(2 row(s) affected)id NewColumn----------- ------------------------------1 NULL2 3.2%, 1.1%, 2.0%(2 row(s) affected)[/CODE]EDIT: Forgot that the "replace" in coalesce was 0.0%, not blank :) |
|
|
|
blessy
Starting Member
6 Posts |
Posted - 2011-09-21 : 02:56:45
|
| THanks for the detailed explanationBlessy K John |
|
|
|
|
|
|
|
|
multiple values to a single column