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 |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2014-06-03 : 09:27:12
|
Hi, I just learned to use following query to convert columns to rows separated by comma.Here is that :Declare @list varchar(max)select @list = isnull(@field+ ',','') + columname from table1select @list This produces the output I want but I'm confused with the statement. I just learnt it by heart. I don't know the meaning of it particular for the statement "select @list = isnull.............. from table1" . What exactly it does to give the desired output?Do we have similar sql exmaples? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-03 : 12:20:44
|
quote:
Originally posted by learning_grsql
Hi, I just learned to use following query to convert columns to rows separated by comma.Here is that :Declare @list varchar(max)select @list = isnull(@field+ ',','') + columname from table1select @list This produces the output I want but I'm confused with the statement. I just learnt it by heart. I don't know the meaning of it particular for the statement "select @list = isnull.............. from table1" . What exactly it does to give the desired output?Do we have similar sql exmaples?
That statement may not be quite right. Perhaps you meant this?select @list = isnull(@list+ ',','') + columname from table1 Assuming that that is the case, you can think of it like this:When you execute a statement such as "select col1 from table1", SQL server retrieves the values of col1 for each row in table1 and returns to you some place where you can see it.When you execute a statement such as "select @list = @list+col1 from table1", SQL Server retrieves the values of each col1 for each row, but instead of returning it to you straight away, appends it to whatever is in @list. It does that for each row, so in effect, the values get concatenated.If you want to have commas in between, you would do "select @list = @list+','+col1 from table1" The isnull(@list+ ',','') just makes sure that when it starts out, the @list being null would be replaced with an empty string. |
 |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2014-06-03 : 17:29:28
|
| Thanks JamesK, It was useful but I still lost somewhere.When I tested with a real table as shown below, the resultset looks different. select [testcolumn] = column1 + column2 from table1The above code does not convert column1 and column2 values in a single row . |
|
|
|
|
|
|
|
|
logic behind comma separated values