Re: Query Resultset

"Lars Tetzlaff" <lars.tetzlaff_at_gmx.net> a écrit dans le message de news: enimi0$sjm$02$1_at_news.t-online.com...
| Muggle wrote:
| > Hello everyone,
| >
| > I need to execute a complex query and I have managed to generate the
| > following resultset
| >
| > ID STATUS VALUE
| > -------------------------------------
| >
| > 1 A value1
| > 1 B value2
| > 1 C value3
| > 2 A value4
| > 2 B value5
| > 2 C value6
| > 3 A value7
| > 3 B value8
| > 3 C value9
| >
| > Using this result I want to generate the following resultset
| >
| > ID ST_A ST_B ST_C
| > --------------------------------------------------------
| > 1 value1 value2 value3
| > 2 value4 value5 value6
| > 3 value7 value8 value9
| >
| > Can I do that using a query ? Or should do it programmatically in the
| > application that retrieves the first resultset ?
| >
| > Thanks in advance
| > Muggle
| >
|
| assuming that your data is stored in mytable i would try
|
| select a.id ID, a.value ST_A, b.value ST_B, c.value ST_C
| from mytable a, mytable b, mytable c
| where a.id = b.id and b.id = c.id
| and a.status = A and b.status = B and c.status = C
|
| Lars

I strongly recommend you follow Daniel advice to have a look at Morgan's Library at www.psoug.org or mine to search for "pivot".

Regards
Michel Cadot Received on Thu Jan 04 2007 - 10:20:21 CST