question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft answered

incorrect syntax near as when make pivot so How to solve it ?

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 ?


sql-server-generalsql-server-transact-sql
· 5
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.

Hi @ahmedsalah-1628,

We recommend that you post CREATE TABLE statements for your table (extractreports.dbo.allfeatures) together with INSERT statements with sample data and the expected result of the sample.

Best regards
Melissa

0 Votes 0 ·

can you help me please to detect issue

0 Votes 0 ·

Hi @ahmedsalah-1628,

I tried to look into your statement but failed to reproduce your issue without data of allfeatures table.

Please provide the DDL and sample data of allfeatures table so that we could reproduce your error and detect this issue faster.

Besides, the prints you provided are truncated. Please provide the complete ones especially the result of 'print @sqldata' which could be great helpful.

Best regards
Melissa

0 Votes 0 ·
Show more comments

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

As already mentioned in this post that in SQL print command that causes it to truncate strings longer than 8000 characters (4000 for nvarchar).

So you could use select @result instead of print @result to report the all columns exist.

Besides, I use the DDL of [ctegroupfeatur] in this complete query but faced below Invalid column name errors in the pivot part.

Msg 207, Level 16, State 1, Line 5
Invalid column name 'value'.

It could be better for you to post the complete DDL and sample data of extractreports.dbo.allfeatures so that we could proceed to reproduce your error and fix it.

Thank you for understanding!

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

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.