why these two statment not give same columns although no different ?

ahmed salah 3,216 Reputation points
2021-04-14T16:25:04.417+00:00

I work on SQL server 2012 i face issue @Header and and @Columns not give me same columns and same

number of column although i get data from table extractreports.dbo.ctegroupfeatur

so why this happen and how to solve that ?
I expect @Header and @columns give me same result

but it different
as you see script result header and columns different why

declare @Columns nvarchar(max)=( select  
    substring(  
        (  
            Select  ',['+FeatureName +']' AS [text()]  
             FROM extractreports.dbo.ctegroupfeatur --with(nolock)   
		  GROUP BY FeatureName,displayorder  
		  ORDER BY  (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc  
             
            For XML PATH ('')  
        ,TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])  
		select @Columns  
--------------------------------------------------  
DECLARE @Header nvarchar(max)=( select  
    substring(  
        (  
            Select  ', '''+FeatureName +''' as ['+FeatureName +']' AS [text()]  
             FROM extractreports.dbo.ctegroupfeatur --with(nolock)   
		  GROUP BY FeatureName,displayorder  
		  ORDER BY  (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc  
             
            For XML PATH ('')  
        ,TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])  
		select @Header  

Script for table ddl and insert as below

87892-ddl-and-insert-statment.txt

87875-script-result.txt

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,477 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
{count} votes

Accepted answer
  1. Viorel 119.9K Reputation points
    2021-04-14T17:23:34.317+00:00

    Use numbers greater than 10000. (Or use STUFF instead of SUBSTRING, according to examples). Note that your sample data do not include some columns that are shown in sample output.

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.