Jake Cube Jake Cube - 7 months ago 29
SQL Question

Pivot table in SQL Server results in error

Currently I'm trying to learn on pivot table, here is my table diagram.

enter image description here

I want to generate data row in branch name and column with month with sum total in sales.

SELECT *
FROM
(SELECT
BRANCH.NAME, SALES.TOTAL, TIME.MONTH
FROM
SALES
INNER JOIN
BRANCH ON SALES.BRANCH_ID = BRANCH.BRANCH_ID
INNER JOIN
TIME ON SALES.TIME_ID = TIME.TIME_ID
) AS TABLE1
PIVOT (
SUM(SALES.TOTAL) FOR TIME.MONTH IN ([APR],[MAY],[JUN])
) PIVOTTABLE


it shows an error:


The column prefix 'SALES' does not match with a table name or alias name used in the query.


Is it my table structure got problem or just my query are wrong?

Answer

Try this:

   SELECT * FROM 
 (
    SELECT  BRANCH.NAME,SALES.TOTAL,TIME.MONTH 
    FROM SALES
    INNER JOIN BRANCH 
    ON SALES.BRANCH_ID=BRANCH.BRANCH_ID
    INNER JOIN TIME 
    ON SALES.TIME_ID=TIME.TIME_ID
  )AS TABLE1
PIVOT (
SUM(TABLE1.TOTAL) FOR TABLE1.MONTH IN ([APR],[MAY],[JUN])
) PIVOTTABLE