By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Translate CrossTab Querys (Please Help!!)

P: n/a
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.

Feb 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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-----
Feb 25 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.