All Rows value into comma seprated values into one single variable

declare @SQLCol nvarchar(max), @TableName nvarchar(max)
select @SQLCol = COALESCE(@SQLCol+’,’,”)+ sc.COLUMN_NAME FROM information_schema.columns sc
where sc.COLUMN_NAME like ‘%ColumnName%’ and sc.TABLE_NAME = @TableName
order by sc.COLUMN_NAME asc

Print @SQLCol

Note:
COALESCE() : This function returns single value of collect all the values with user defined format. Here for each records i used ‘,’ but you can use anything you want for seperation. This is very good function provided by SQL server.