I have written this query to concatenate all the columns together but its always throwing this error: query:

DECLARE @SCHEMA VARCHAR(25) = 'PERSON'
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON'
DECLARE @I INT =1
DROP TABLE IF EXISTS #COLUMNS
SELECT
  ORDINAL_POSITION,
  COLUMN_NAME
INTO #COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME

DECLARE @NUMBER_OF_COLUMN INT = (SELECT COUNT(*) FROM #COLUMNS)

DECLARE @COLUMNLIST NVARCHAR(MAX)
WHILE @I<@NUMBER_OF_COLUMN
BEGIN
    SET @COLUMNLIST = ISNULL(@COLUMNLIST,
        (SELECT COLUMN_NAME
         FROM #COLUMNS
         WHERE ORDINAL_POSITION = @I
        ))   ', '  
        (SELECT COLUMN_NAME
         FROM #COLUMNS
         WHERE ORDINAL_POSITION = @I)
    SET @I = @I   1
END

DECLARE @QUERY NVARCHAR(MAX) = 'SELECT CONCAT(' @COLUMNLIST ') FROM Person.Person'
EXEC (@QUERY)

Error:

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

Error seems to be from this line of code:DECLARE @QUERY NVARCHAR(MAX) = 'SELECT CONCAT(' @COLUMNLIST ') FROM Person.Person'

How do I solve it?

CodePudding user response：

DECLARE @SCHEMA VARCHAR(25) = 'PERSON';
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON';

DECLARE @columnlist varchar(max);

SELECT @columnlist = string_agg(COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
   TABLE_SCHEMA = @SCHEMA 
   AND TABLE_NAME = @TABLE_NAME;

SELECT @columnlist;

CodePudding user response：

Building on Mitch Wheat noted you will need to convert all the Columns to text first. Using the Cast(col as varchar(max))

Ie can you just use

DECLARE @SCHEMA VARCHAR(25) = 'PERSON';
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON';

DECLARE @columnlist varchar(max);

Select  @columnlist = string_agg(ColAsString, ', ') from (
select 'CAST('   COLUMN_NAME   ' as nvarchar(max))' as ColAsString
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
   TABLE_SCHEMA = @SCHEMA 
   AND TABLE_NAME = @TABLE_NAME) as Data

--SELECT @columnlist;

Declare @SQL as NVARCHAR(max)
Select  @SQL = 'Select CONCAT('   @columnlist   ' ) as ALLINONE from '   @SCHEMA   '.'   @TABLE_NAME

Exec(@SQL)

CodePudding user response：

Most likely you are getting the error because the table previously contained XML.

You don't need a temp table at all. You can just use STRING_AGG.

• You should also quote the names using QUOTENAME.
• Object names should be in variables declared as sysname which is an alias for nvarchar(128)
• You should use sp_executesql which also allows you to pass parameters.
• INFORMATION_SCHEMA.COLUMNS is for compatibility only. Use sys.columns instead.

DECLARE @SCHEMA sysname = 'Person';
DECLARE @TABLE_NAME sysname = 'Person';

DECLARE @sql nvarchar(max) = '
SELECT '   (
    SELECT STRING_AGG(QUOTENAME(c.name), ',
  ')
    FROM sys.columns c
    JOIN sys.tables t ON t.object_id = c.object_id
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE t.name = @TABLE_NAME
      AND s.name = @SCHEMA
)   '
FROM '   QUOTENAME(@SCHEMA)   '.'   QUOTENAME(@TABLE_NAME)   ';';

EXEC sp_executesql @sql;  -- can also pass parameters