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
file insert and ddl