Sarma Vadlamani
asked on
oracle query
I have a requirement as follows eg
to get in a single row in 3 different columns. i will use this query in oracle report
dept no
10
20
30
as
dept1 dept2 dept3
10 20 30
thanks
to get in a single row in 3 different columns. i will use this query in oracle report
dept no
10
20
30
as
dept1 dept2 dept3
10 20 30
thanks
ASKER
it is static only for 3 rows
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
that is, if an extra row "40" shows up, a sql query won't automatically create a 4th column for you.
but for a static set of columns, simply number them and then use conditional aggregates to pivot
SELECT MAX(CASE WHEN rn = 1 THEN deptno END) dept1,
MAX(CASE WHEN rn = 2 THEN deptno END) dept2,
MAX(CASE WHEN rn = 3 THEN deptno END) dept3
FROM (SELECT deptno, ROW_NUMBER() OVER (ORDER BY deptno) rn FROM dept);