CODEWITHSUNDEEP
sql-serverms-accesspivot
San
San

Reputation: 23

Convert MS Access Crosstab query to SQL Server stored procedure

I'm trying to convert MS Access Cross Tab query into SQL Server stored procedure but having issue with pivoting data in SQL Server.

Here's the MS Access cross query I want to convert -

TRANSFORM Sum(NZ(Actuals!Amount,0)) AS Amount
SELECT Actuals.PS_OV, Actuals.Period, Actuals.Program, Actuals.Actuals_Year
FROM Actuals
GROUP BY Actuals.PS_OV, Actuals.Period, Actuals.Program, Actuals.Actuals_Year
PIVOT Actuals.Source;

Values from the Source column (i.e. Equipment, Expense, Furniture, Leasehold) are pivoted to columns. Kindly advise how to do this in a SQL Server stored procedure?

Many thanks.

Upvotes: 2

Views: 2839

Answers (1)

Taryn
Taryn

Reputation: 247710

In SQL Server your PIVOT would like be like this:

SELECT *
FROM 
(
  SELECT PS_OV
    , Period
    , Program
    , Actuals_Year
    , IsNull(Amount, 0) as Amount
    , Source
  FROM Actuals
) x
PIVOT
(
  Sum(Amount)
  FOR Source IN ([Equipment], [Expense], [Furniture], [Leasehold])
) p

Upvotes: 3

Related Questions