Pivot and Transform not working in SQL Server

[subir.kumar]

Hi,

I am migrating my access database to SQL Server. I have a
crosstab query in Access which is

TRANSFORM Sum(qryDatafile2003.[Total Price]) AS
[SumOfTotal Price]

SELECT qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client], First
(qryDatafile2003.[Product Name]) AS [FirstOfProduct Name],
First(qryDatafile2003.[Sub Product Name]) AS [FirstOfSub
Product Name]

FROM qryDatafile2003 INNER JOIN tblExpenseCodes ON
qryDatafile2003.[Long Code] = tblExpenseCodes.[long code]

GROUP BY qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client]

PIVOT Format([Billing Cycle Name],"mmm") & "-" & Format
([Billing Cycle Name],"yy") In ("Apr-03","May-03","Jun-
03","Jul-03","Aug-03","Sep-03","Oct-03","Nov-03","Dec-
03","Jan-04","Feb-04","Mar-04");


The Pivot and Transform dont seem to work. Can anyone
please suggest me how to write this query in SQL Server.
Thanks in advance for your assistance.

Regards,

Subir

 

[Lyle Fairfield]

Hi,

I am migrating my access database to SQL Server. I have a
crosstab query in Access which is

SQL-Server has a help utility called Books On Line (BOL). You should
read it.

Here is part of one of the entries about PIVOT:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year

But you need to understand that you are NO LONGER using Jet SQL and that
you must use Transact-SQL (T-SQL) and that they can be quite different
in many areas. Spend a few weeks with BOL and you will be amazed at the
power of T-SQL and Stored Procedures.

 

[Jack D. Ripper]

For all kinds of crosstabs on S2k check out
the RAC utility.Similar to Access crosstab but
much more powerful with many features/options.
Easy to use, no complicated sql necessary.

www.rac4sql.net