I work on sql server 2012 when implement pivot i get error
incorrect synatx near as but i don't know what is issue
this is my code :
sql script printed
error i get
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.
print sql statement
'Competitor Number of Outputs' as [Competitor Number of Outputs], 'Competitor Output Type' as [Competitor Output Type], 'Competitor Output Voltage' as [Competitor Output Vol
[Competitor Number of Outputs],[Competitor Output Type],[Competitor Output Voltage],[Competitor Rail-to-Rail],[NXP Acceleration Range],[NXP Capacitance Ratio],[NXP M
SELECT * Into ##FinalTable
FROM extractreports.dbo.allfeatures with(nolock)
PIVOT(max(Value) FOR [FeatureName] IN ([Competitor Automotive],[NXP Automotive],[Competitor Normalized Package Name],[NXP Normalized Package Name],[Competitor Accelerometers Type],[Competitor Amplifier Type],[Competitor Battery Type],[Competitor Diode Type],[Competitor Maxim
as above text of features not complete so how to solve this issue please ?
What I have tried:
DECLARE @result NVARCHAR(MAX)
DECLARE @col NVARCHAR(MAX)
DECLARE @sqldata NVARCHAR(MAX)
set @result =
(
SELECT SUBSTRING((Select ',['+FeatureName +']' AS [text()]
FROM extractreports.dbo.allfeatures 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])
print @result
set @col =( select
substring(
(
Select ', '''+ FeatureName +''' as ['+ FeatureName, +']'
FROM extractreports.dbo.allfeatures 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])
print @col
select @sqldata =CONCAT('
SELECT * Into ##FinalTable
FROM extractreports.dbo.allfeatures with(nolock)
PIVOT(max(Value) FOR [FeatureName] IN ('+@result+')) AS PVTTable
',
N' select ''[NXPPartId]'' as ''NXPPartId'',''[comptitorPartId]'' as ''comptitorPartId'',''[zplid]'' as ''zplid'',''CompetitorPartNumber'' as ''CompetitorPartNumber'' ,''CompetitorCompany'' as ''CompetitorCompany'',''Competitors'' as ''Competitors'',''NXPPartNumber'' as ''NXPPartNumber'',''Cross Grade'' as ''Cross Grade'',''ProductName'' as ''ProductName'',''ExecutionDate'' as ''ExecutionDate'', ' +@col + '
union all
SELECT cast([NXPPartId] as Varchar(12)) as NXPPartId,cast([comptitorPartId] as Varchar(12)) as comptitorPartId,cast([zplid] as Varchar(12)) as zplid,[CompetitorPartNumber],[CompetitorCompany],[Competitors],[NXPPartNumber],[CrossGrade],[ProductName],cast(ExecutionDate as Varchar(12)) as ''ExecutionDate'', ' +@result + ' FROM ##FinalTable
group by
[NXPPartId],
[comptitorPartId],
[zplid],
[CompetitorPartNumber],
[CompetitorCompany],
Competitors,
[NXPPartNumber],
[CrossGrade] ,
[ProductName],
ExecutionDate
')
print @sqldata
EXEC (@sqldata)
Submit an article or tip