Help on sql querry

[omegamark]

I need to write a sql querry where is

i need to get the records like

deptno empno empno empno empno
10 23 24 25 27

[omegamark]

let we write the stament clearly

Normally when we select we get records like

empno deptno
34 10
35 10
36 10
37 20
38 20
39 20

i need to get the records like

deptno empno empno empno
10 34 35 36
20 37 38 39


In access we will be able to do this .how about this in oracle in s sql statement.

[marist89]

Personally, I'd do it with a Package. jmodic, chrisrlong or others may have some fancy decode/union/case statement...

[Heath]

Yeah, we play around with DECODE, CASE, etc. to do <i>bounded pivot</i> queries. That is, we can very efficiently pivot the results from many rows into a single row assuming that we know the maximum number of rows to be pivoted. If there is no known bound, then the best solution that I've seen is to use a procedure like Jeff suggested.

Some threads discussing bounded pivot solutions:

&nbsp;&nbsp;&nbsp; <a href="http://www.dbasupport.com/forums/showthread.php?threadid=13920">Using CASE</a>. (It's not the main post of the post, but it's in there.)

&nbsp;&nbsp;&nbsp; <a href="http://www.dbasupport.com/forums/showthread.php?threadid=9581">Using math tricks</a>.

&nbsp;&nbsp;&nbsp; Using DECODE. (I can't find one right now, but you should be able to figure it out based upon the others).

Also, the discussion between Jurij and Chris <a href="http://www.dbasupport.com/forums/showthread.php?threadid=13681">in this thread</a> about DECODE vs. CASE is quite educational.

And here is a <a href="http://www.dbasupport.com/forums/showthread.php?threadid=13492">thread</a> where Jeff provided a procedure to do this.


HTH,

Heath

[marist89]

Originally posted by Heath
Also, the discussion between Jurij and Chris <a href="http://www.dbasupport.com/forums/showthread.php?threadid=13681">in this thread</a> about DECODE vs. CASE is quite educational.

When these two get into it, watch out. It's a regular battle of the minds...