Flattening a result

From: Jochen Wiedmann <jochen.wiedmann_at_freenet.de>
Date: 5 Oct 2004 00:56:31 -0700
Message-ID: <c66df65e.0410042356.5e6c6bbc@posting.google.com>

Hi,

thanks to Renee, I solved one of my previous questions. I am now able to fetch the first row and the number of items using the query

  SELECT a.aId, aName, aVorname, aFilter, cnt FROM DBAkte a JOIN   (SELECT aAktenId, aName, aVorname, aFilter,

     count(*) over (partition by aAktenId, aFilter) cnt,
     row_number() over (partition by aAktenId, aFilter order by b.aId) r,
     b.aId bId FROM DBBeteiligte b WHERE aFilter = 'x')

  ON aAktenId=a.aId AND r=1

So far, this is fine. Unfortunately, I have to perform this query not only for the case 'x', but for other values as well. In other words, I would like to replace

  aFilter = 'Klaeger'

with

  aFilter IN ('x', 'y', 'z)

and convert the result

        a.aId, aName, aVorname, aFilter, cnt

        7      a      d         x        1
        7      b      e         y        3
        7      c      f         z        2
        8      g      h         x        2
        ...

into

        a.aId xName xVorname xCnt yName yVorname yCnt zName zVorname zCnt
        7     a     d        1    b     e        3    c     f        2

Of course, I can easily achieve the result by performing the first query above three times. However, I have the feeling, that it could be far more efficient. In particular, because there might be more than three different values for "aFilter".

Regards,

Jochen

P.S: On a related topic: My first query uses the same "partition by" clause twice, which is possibly slowing things down. Is it possible to share these clauses for multiple columns? Received on Tue Oct 05 2004 - 02:56:31 CDT