Translate CrossTab Querys (Please Help!!)

[rguarnieri]

I have this query in access and I need to translate to SQL ansi

TRANSFORM Sum(BilletSCHQTY) AS SumOfBilletSCHQTY
SELECT Tap,
Mandrin,
BilletOD,
BilletLength,
FROM Tbl_ExtruOptimize_Src
GROUP BY Tbl_ExtruOptimize_Src.Tap,
Tbl_ExtruOptimize_Src.Mandrin,
Tbl_ExtruOptimize_Src.BilletOD,
Tbl_ExtruOptimize_Src.BilletLength,
ORDER BY Tbl_ExtruOptimize_Src.Tap,
Tbl_ExtruOptimize_Src.Mandrin,
Tbl_ExtruOptimize_Src.BilletOD,
Tbl_ExtruOptimize_Src.BilletLength,
PIVOT [Org_StartDate];

The result show Tap, Mandrin, BilletOD and BilletLength grouped by
Sum(BilletSCHQTY) for the differents dates. I need to change this query
for an ansi query.

Thank you.

[MGFoster]

rguarnieri wrote:

I have this query in access and I need to translate to SQL ansi

TRANSFORM Sum(BilletSCHQTY) AS SumOfBilletSCHQTY
SELECT Tap,
Mandrin,
BilletOD,
BilletLength,
FROM Tbl_ExtruOptimize_Src
GROUP BY Tbl_ExtruOptimize_Src.Tap,
Tbl_ExtruOptimize_Src.Mandrin,
Tbl_ExtruOptimize_Src.BilletOD,
Tbl_ExtruOptimize_Src.BilletLength,
ORDER BY Tbl_ExtruOptimize_Src.Tap,
Tbl_ExtruOptimize_Src.Mandrin,
Tbl_ExtruOptimize_Src.BilletOD,
Tbl_ExtruOptimize_Src.BilletLength,
PIVOT [Org_StartDate];

The result show Tap, Mandrin, BilletOD and BilletLength grouped by
Sum(BilletSCHQTY) for the differents dates. I need to change this query
for an ansi query.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you mean not using JET ANSI 92 and the SQL u're using has the CASE
expression then you'd do something like this:

SELECT Tap,
Mandrin,
BilletOD,
BilletLength

SUM(CASE
WHEN Org_StartDate BETWEEN '2006-01-01' AND '2006-03-31'
THEN BilletSCHQTY
ELSE NULL
END) As Q1,

SUM(CASE
WHEN Org_StartDate BETWEEN '2006-04-01' AND '2006-06-30'
THEN BilletSCHQTY
ELSE NULL
END) As Q2,
... etc. for other quarters ...

FROM Tbl_ExtruOptimize_Src
GROUP BY Tap,
Mandrin,
BilletOD,
BilletLength
ORDER BY Tap,
Mandrin,
BilletOD,
BilletLength
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ//JZYechKqOuFEgEQKrAgCfQolems/UUDbL8iAI4qOg0yonym8AoI3w
R75T+Cpy3oHTH1pCG9G7XG/6
=jupz
-----END PGP SIGNATURE-----