Get the column name with comma separated in SQL SERVER | Part 20(
Get the column name with comma separated in SQL SERVER:
Here we can learn how to get the column name with comma separation
Consider we have one table employees,there is only 3 columns there, so you can easily copy and past after each coluun you can put the commas.
CREATE TABLE [employees](
[empid] [bigint] NOT NULL,
[Name] [nvarchar](500) NULL,
[salary] [float] NULL,
PRIMARY KEY CLUSTERED
(
[empid] ASC
)
) ON [PRIMARY]
In case of a table which have more than 50 columns, so need to put commas after each column, it is time consuming, so we can write a query so that we can achieve column name with comma’s separation.
Query:
select t.name Table_Name,
STUFF ((
select ',' + c.name
from sys.columns c
join sys.tables tt on tt.object_id = t.object_id and t.object_id = c.object_id
join sys.schemas s on tt.schema_id = s.schema_id
order by t.name, c.column_id
for xml path('')), 1, 1, '') as columns
from sys.tables t
where t.name = 'employees'
Now create a table in which columns is more than 30.
--create table test_employee (empid int, emp_name nvarchar(200))
--drop table test_employee
declare @i int
declare @sql NVARCHAR(MAX)
set @i=1
while @i put here less sign =50
begin
set @sql='alter table test_employee add attendace'+CAST(@i AS NVARCHAR)+' char(1)'
PRINT @sql
exec (@sql)
set @i=@i+1
end
then after write the query to get the column name with commas separated:
select t.name Table_Name,
STUFF ((
select ',' + c.name
from sys.columns c
join sys.tables tt on tt.object_id = t.object_id and t.object_id = c.object_id
join sys.schemas s on tt.schema_id = s.schema_id
order by t.name, c.column_id
for xml path('')), 1, 1, '') as columns
from sys.tables t
where t.name = 'test_employee'