CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Simon
Simon

Reputation: 11

PIVOT query in SQL

I am getting an error with the below sql statement. Error reads:

Error in FROM clause: near 'SELECT' Unable to parse query

Error Message: Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ')'

Any ideas as to why?

SELECT *
FROM (
  SELECT [Order], Dept, Area, [Final Week], Total
 FROM dbo.DeptSummary10Weeks
) AS SourceTable 
PIVOT (Sum(Total) FOR [Final Week] IN (SELECT WeekNum FROM dbo.[10Weeks]) AS PivotTable;

Upvotes: 0

Views: 70

Answers (2)

Saba
Saba

Reputation: 71

You have to give some empty value for Final Week 

  SELECT [Order], Dept, Area,'' as [Final Week], Total
     FROM dbo.DeptSummary10Weeks

Just try following way...

Declare @var nvarchar(max)
Select @var = coalesce(@var +',','') + cast(WeekNum as nvarchar(20)) from dbo.[10Weeks]    
FOR [Final Week] IN ('+@var+') AS PivotTable;

Upvotes: 0

StepUp
StepUp

Reputation: 38209

Try to use explicit WeekNum and add closing bracket in your query:

SELECT *
FROM (
  SELECT [Order], Dept, Area, [Final Week], Total
 FROM dbo.DeptSummary10Weeks
) AS SourceTable 
PIVOT (Sum(Total) FOR [Final Week] IN ([1], [2], [3], [4], [5], [6], [7])) AS PivotTable

UPDATE:

Try to use dynamic version of pivot:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ' , ' + CONCAT('[', CONVERT(varchar(10),  t.WeekNum), ']')
      from dbo.[10Weeks] t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')

select  @sql = '
    SELECT *
    FROM (
         SELECT [Order], Dept, Area, [Final Week], Total
         FROM dbo.DeptSummary10Weeks
    ) AS SourceTable 
    PIVOT (Sum(Total) FOR [Final Week] IN (' + @cols + ') ) AS PivotTable'   

exec(@sql);

Upvotes: 1

Related Questions