My requirement is that i have Table with 5 columns. What I want is to fetch the values from the row and want to store all 5 values in variable with comma separated.
table Name: table1
col1 col2 col3 col4 col5
1 2 3 4 5
Result should be
1,2,3,4,5
Note: I dont want to mention column name like if we use Concat function there we need to mention columns, but here my requirement is I'll only have the table name
Use the table : information_schema.columns
DECLARE @col1 varchar(10)
DECLARE @col2 varchar(10)
DECLARE @col3 varchar(10)
DECLARE @col4 varchar(10)
DECLARE @col5 varchar(10)
SET @col1 = (select column_name from information_schema.columns where table_name = 'table1' and ordinal_position = '1')
SET @col2 = (select column_name from information_schema.columns where table_name = 'table1' and ordinal_position = '2')
SET @col3 = (select column_name from information_schema.columns where table_name = 'table1' and ordinal_position = '3')
SET @col4 = (select column_name from information_schema.columns where table_name = 'table1' and ordinal_position = '4')
SET @col5 = (select column_name from information_schema.columns where table_name = 'table1' and ordinal_position = '5')
DECLARE @sqlText nvarchar(1000);
SET @sqlText = N'SELECT ' + @col1 + ',' + @col2 + ',' + @col3 + ','+ @col4 + ','+ @col5 +' FROM dbo.table1'
Exec (@sqlText)
If you always have exactly 5 columns in your table, this would work. Note this doesn't refer to the column names
;WITH cte(a,b,c,d,e) as
(
-- can test with this:
--SELECT * FROM (values(1,2,3,4,5)) x(col1,col2,col3,col4,col5)
SELECT * FROM table1
)
SELECT
concat(a, ',', b, ',', c, ',', d, ',', e)
FROM
cte
Result:
1,2,3,4,5
I am always hesitant about giving answers that suggest dynamic SQL, often when dynamic SQL is required, the problem is best solved outside of SQL. Erland Sommarskog has written pretty extensively on the subject - The Curse and Blessings of Dynamic SQL
Anyway, your problem can be solved using dynamic SQL. You can build your SQL using the system views, and concatenate the column name using SQL Server's XML extensions:
DECLARE @TableName SYSNAME = 'dbo.table1';
DECLARE @SQL NVARCHAR(MAX) =
'SELECT CONCAT(' + STUFF(( SELECT ','','',' + QUOTENAME(c.Name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@TableName, 'U')
ORDER BY Column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') + ')
FROM ' + @TableName + ';';
EXECUTE sp_executesql @SQL;
So for this table:
CREATE TABLE dbo.Table1
(
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 INT,
Col4 INT,
Col5 VARCHAR(30)
);
The SQL that is generated is:
SELECT CONCAT([Col1],',',[Col2],',',[Col3],',',[Col4],',',[Col5])
FROM dbo.table1;
STUFF function. The subquery generates code like ,',',[Col1],',',[Col2],',',[Col3], STUFF is used to remove the first comma, so it removes the first 5 characters - ,',',, and replaces them with ''.Just replace the name of your table for variable @tablename and you should be fine.
NULL values will be translated to a 'NULL' string
SET NOCOUNT ON
DECLARE @tablename nvarchar(50) = 'atable'
DECLARE @colList nvarchar(max)
DECLARE @sql nvarchar(max)
--Select list of colum names
select @colList = coalesce('CAST('+@Collist+'+'', '' ' +'+ CAST(','')+name+' as nvarchar)'
from
(
select name
from sys.columns
where [object_id] = OBJECT_ID(@tablename)
) sub
SET @colList = REPLACE(REPLACE(@colList, 'CAST(', 'ISNULL(CAST('), 'nvarchar)', 'nvarchar), ''NULL'')')
SET @sql = 'SELECT '+@colList+' FROM '+@tablename
EXEC(@sql)
Example: This table (atable)
col1 col2
-------------------------------------------------- -----------
foo 1
bar 2
abc 3
def 4
ghi 5
yep NULL
will be transformed to
--------------------------------------------------------------
foo, 1
bar, 2
abc, 3
def, 4
ghi, 5
yep, NULL
returns 1 result per row of the original table.
VARCHAR2an Oracle datatype? I see the question tagged with SQL Server.want to store all 5 values in variable with comma separated<-- WHY???