Pivot and Transform not working in SQL Server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

subir.kumar@citigroup.com - 24 May 2004 20:53 GMT

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

Reply to this Message

Lyle Fairfield - 24 May 2004 23:03 GMT

> 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.

Reply to this Message

Jack D. Ripper - 25 May 2004 14:34 GMT

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

Reply to this Message