Re: Flattening a result

> 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

The keyword is pivot query and you should find examples for it on the web. They're implented with decode(...) or case when ... constructs

By the way, my name is Rene, with an accent aigue on the 2nd e, not Renee.

hth

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/