April 12, 2021 at 7:11 am
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 :
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
--PRINT @Header
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)
sql script printed
error ig get
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.
print sql
'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 ?
April 12, 2021 at 8:36 am
The simplest method to debug dynamic SQL is PRINT/SELECT it, debug that SQL and then propagate the solution to the SQL that produces the dynamic statement. We can't run your SQL, so we can't do that to see what is generating the error. If you don't understand the generated SQL, then post that as well.
Also, as a side note, '[' + {Value} + ']' is not safe from injection; if you want to safely inject a value use QUOTENAME. Also, personally, you may find it easier to pivot the data with a Cross Tab/Conditional Aggregation rather than the restrictive PIVOT operator (which can be a little confusing to read as well). Finally, I recommend using sys.sp_executesql @SQL over EXEC(SQL) . The former can be parametrised when you need it, where as the latter can't, so it promotes good, and safer, writing standards.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 12, 2021 at 7:59 pm
Just a quick thing from my eyeballing on it - it doesn't appear to me that the query you provided gives the results you indicated.
@result doesn't contain the "AS" keyword, but @col does.
First thing I'd do is remove all PRINT statements EXCEPT print @sqldata and make sure that that information looks like what you are expecting. If it is too long (I think PRINT only allows 8000 characters... might be less), I'd use SELECT and then paste that into something like notepad to review.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 13, 2021 at 7:54 am
If it is too long (I think PRINT only allows 8000 characters... might be less), I'd use SELECT and then paste that into something like notepad to review.
Just for confirmation, PRINT will "print" up to 8,000 bytes of characters, rather 8,000 characters. So, as @sqldata is defined as an nvarchar(MAX), that would be the first 4,000 characters. For a varcharthis'll normally be 8,000 characters, unless you're using a UTF-8 collation. PRINT (Transact-SQL): Remarks
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply