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.
why these two statment not give same columns although no different ?
ahmed salah
3,216
Reputation points
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