Why does this T-SQL query run this way?

Question

why does this code below return a single row of combined column names, when I would expect 3 rows?

----------------
create table PERM_TABLE (
COLUMN_A varchar(1) ,
COLUMN_B varchar(1) ,
COLUMN_C varchar(1) )


declare @select_values varchar(1000)

set @select_values = null

-- Get the Insert/Select columns
select @select_values = coalesce(@select_values + ', ', '') + '[' + column_name + ']'
from information_schema.columns
where table_name = 'PERM_TABLE'

select @select_values
----

Thanks Guys - I'm still missing something here - I don't see how/where that call is combining three rows from information_schema.columns, into 1 single concatenated row.

i.e. why does the code snippet below not work in the same way?

----------------
create table #temp (
TABLE_NAME varchar(50) null ,
COLUMN_LIST varchar(1000) null )

insert into #temp ( TABLE_NAME ) select 'PERM_TABLE'

update #temp
set COLUMN_LIST = coalesce(COLUMN_LIST + ', ', '') + '[' + column_name + ']'
from #temp t
inner join [information_schema].[columns] i on t.table_name = i.Table_Name


Thanks!

Answer 1

coalesce: returns the first nonnull expression among its arguments.

Answer 2

@select_values is defined as a varchar(1000) and will not be able to store multiple rows.

Is this what you are trying to do?

declare @select_values table (
column_values varchar(1000)
)

-- Get the Insert/Select columns
insert @select_values
select '[' + column_name + ']'
from information_schema.columns
where table_name = 'PERM_TABLE'

select * from @select_values

Answer 3

The Coalesce function returns the FIRST non null value. Not ALL of the values.