need help to understand two pieces of codes

[Paul T. Rong]

Who can help me to understand the following two queries? They are very
useful when you want to show data horizontally. I found it from a Chinese
Access website.

----------------------------------------------------------------------------
--
Sql codes:

query1

SELECT DCount("*","table1","[id]<=" & [id]) AS ID, table1.Bank,
Table1.Amount
FROM table1;

query2

SELECT Max(IIf([ID] Mod 5=1,[Bank])) AS Bank1, Sum(IIf([ID] Mod
5=1,[Amount])) AS Amount1, Max(IIf([ID] Mod 5=2,[Bank])) AS Bank2,
Sum(IIf([ID] Mod 5=2,[Amount])) AS Amount2, Max(IIf([ID] Mod 5=3,[Bank])) AS
Bank3, Sum(IIf([ID] Mod 5=3,[Amount])) AS Amount3, Max(IIf([ID] Mod
5=4,[Bank])) AS Bank4, Sum(IIf([ID] Mod 5=4,[Amount])) AS Amount4,
Max(IIf([ID] Mod 5=0,[Bank])) AS Bank5, Sum(IIf([ID] Mod 5=0,[Amount])) AS
Amount5
FROM query1
GROUP BY ([ID]-1)\5;
----------------------------------------------------------------------------
------

For me it is very difficult to understand
1£®GROUP BY ([ID]-1)\5
2£®Max(IIf([ID] Mod 5=1,[Bank]))
many thanks,

Paul

[John Winterbottom]

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:O5******************@news.chello.at...

Who can help me to understand the following two queries? They are very
useful when you want to show data horizontally. I found it from a Chinese
Access website.

This is one way of doing crosstabs in sql engines that don't support
crosstabs directly. With Jet you have the TRANSFORM .. PIVOT statement which
is neater and probably faster as well. Incidentally, the next version of SQL
Server, (Yukon, now in beta), also supports PIVOT (at last).

[Paul T. Rong]

Thanks for infomation. It is quite slow indeed. I decided not to use them.
Paul
"John Winterbottom" <as******@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:2t*************@uni-berlin.de...

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:O5******************@news.chello.at...

Who can help me to understand the following two queries? They are very
useful when you want to show data horizontally. I found it from a Chinese Access website.

This is one way of doing crosstabs in sql engines that don't support
crosstabs directly. With Jet you have the TRANSFORM .. PIVOT statement

which is neater and probably faster as well. Incidentally, the next version of SQL Server, (Yukon, now in beta), also supports PIVOT (at last).