-1

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Anshu Kumar
  • 51
  • 1
  • 7

4 Answers4

3

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)
President Camacho
  • 1,860
  • 6
  • 27
  • 44
3

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
0

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;
Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • hi, thanks for ur reply , but can you please tell me what is that 1,5 in this line (FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '')) – Anshu Kumar Jan 29 '15 at 13:53
  • It is part of the `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 `''`. – GarethD Jan 29 '15 at 13:57
  • thnks @GarethD this is working but the only problem is if in any column value is null then it is showing empty for tht position Ex: if in table values are like 1 2 null 3 4 then result is getting 1,2,,3,4 – Anshu Kumar Jan 29 '15 at 14:11
  • is there any wat such that it should show null over there – Anshu Kumar Jan 29 '15 at 14:20
0

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.

mepp
  • 1
  • 2