Suppose we have two tables:
Table1:
--------------
ID Field1 Field2
1 111 AAA
2 222 BBB
3 333 CCC
...
Table2:
-------------
Field1 Field3
111 1
222 1
111 2
111 3
111 4
111 6
222 3
333 1
I need table where I can see all fields from Table1 and (for example) 4 fields, first representing largest value of Field3 from table3, second - largest, which is less than largest and so on.
For example:
Result will be:
--------------------------------------------------
Field1 Field2 Field3A Field3B Field3C Field3D
111 AAA 6 4 3 2
222 BBB 3 1 NULL NULL
333 CCC 1 NULL NULL NULL
How to make it? It's kind of pivot table, I suppose.
Giedrius
Giedrius