Originally posted by ggnanaraj

Can you eloborate on the above with an example, links please?
Consider a simple example where in I need to convert the 5 level of rows to 5 diff cols....

Code:
Select p1.ProductId        ,
       p1.ParentProductId  
from   am_cd_ProductHierarchy p1
where   p1.ProductLevel = 'S'

O/P

ProductId ParentProductId

11        111              -- p1 ( Logical )
111       1111             -- p2     "
1111      11111            -- p3     "
11111     111111           -- p4     "
111111    1111111          -- p5     "



Now if u want in rows....


select  p1.ProductId Sku           ,
        p1.ParentProductId Model   ,
        p2.ParentProductId Product ,
        p3.ParentProductId Family  ,
        p4.ParentProductId Line    ,
        p5.ParentProductId TypeCd  
from    am_cd_ProductHierarchy p1,
        am_cd_ProductHierarchy p2,
        am_cd_ProductHierarchy p3,
        am_cd_ProductHierarchy p4,
        am_cd_ProductHierarchy p5
where   p1.ProductLevel = 'S'
and	p1.ParentProductId = p2.ProductId
and	p2.ParentProductId = p3.ProductId
and	p3.ParentProductId = p4.ProductId
and	p4.ParentProductId = p5.ProductId


O/P

Sku   Model  Product  Family  Line    Typecd.

11    111    1111     11111   111111  1111111
Now u see, the rows actually have been converted to cols.....

But as i see in ur case...u may have to develop dynamic query builder to obtain this result set U dont know which level of ROWS have to converted to Corresponding Cols....

say if the query
Code:
select b.employee_name, a.activity_occured_date, sum(a.timespent)
   from d_ts_entries a, u_hris.d_hris_personal b
   where a.fk_employee_id = b.employee_id and to_char(a.activity_occured_date, 'MM') = '04' AND
   TO_CHAR(a.activity_occured_date, 'YYYY') = '2003'
   group by b.employee_name, a.activity_occured_date;
might return 4 rows at one point of time then u have to have 4th level of convertion from rows to cols....

now if it returns 6 rows then 6th level is required and hence dynamic query builder is necessary in ur case as against my example of having fixed 5th level convertion...

Its not difficult, but at the same time not easy....

So rather go for some front end tools which will do this for u (Say VB or VBS or so )...and then save it in some text format...pick this result from Ur Crystal Reports rather than picking from DB directly...( Not sure though u can pick result set from text formats via Crystal reports. )

Regards
Abhay.