question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked TomPhillips-1744 edited

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

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

They are returning the results expected from the query you posted.

What exactly do you think is wrong?

I believe the problem you are having is the data is truncated? Your output text for @Header is larger than @Columns. If this exceeds 10000 chars, it is being truncated.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.