ABHIDHAN

 Profile Answers by ABHIDHAN 

• Feb 18th, 2008

 

using decode function u can do that easily....

let me give u an example of 2 rows n 2 column

x  y
1  a
2   b

select decode(y,a,1,b,a) x,decode(y,a,2,b,b) y from table;

result will be like this

x  y
1  2
a  b

cheers......

sampra

 Profile Answers by sampra 

• Feb 26th, 2008

 

nice question giv some good answer

arun_doss

 Profile Answers by arun_doss 

• Mar 12th, 2008

 

use the below query

select deptno,
            max(decode(rn,1,ename))||
            max(decode(rn,2,','||ename))||
            max(decode(rn,3,','||ename))||
           max(decode(rn,4,','||ename))||
            max(decode(rn,5,','||ename))||
            max(decode(rn,6,','||ename)) ename, loc
    from (select emp.deptno, ename, loc,
                  row_number () over (partition by emp.deptno order by ename) rn
           from emp, dept where emp.deptno = dept.deptno)
     group by deptno, loc

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 21st, 2011

 

None of the previous answers actually answered the question. With the detailed information as a given, there are a number of ways in Oracle PL/SQL you can do this, and the following is just one way with a single SELECT statement.

Mark

Code
SELECT CASE 


         WHEN Colmn = 'DeptId' THEN 


             (SELECT RTRIM(XMLAGG(XMLELEMENT(e, DeptId || ' ')).EXTRACT ('//text()')) FROM Department)


         WHEN Colmn = 'Dname' THEN 


             (SELECT RTRIM(XMLAGG(XMLELEMENT(e, Dname || ' ')).EXTRACT ('//text()')) FROM Department)


         WHEN Colmn = 'Dlocation' THEN 


             (SELECT RTRIM(XMLAGG(XMLELEMENT(e, Dlocation || ' ')).EXTRACT ('//text()')) FROM Department)


       END ChrData


FROM (SELECT TabColmn.Colmn


            FROM (SELECT 'DeptId' AS Colmn FROM dual


                       UNION ALL


                      SELECT 'Dname' AS Colmn FROM dual


                       UNION ALL


                      SELECT 'Dlocation' AS Colmn FROM dual


          ) TabColmn


      )

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 22nd, 2011

 

The code seemed to have been messed up there, here it is again formated:

SELECT CASE
WHEN Colmn = 'DeptId' THEN
(SELECT RTRIM(XMLAGG(XMLELEMENT(e, DeptId || ' ')).EXTRACT ('//text()')) FROM Department)
WHEN Colmn = 'Dname' THEN
(SELECT RTRIM(XMLAGG(XMLELEMENT(e, Dname || ' ')).EXTRACT ('//text()')) FROM Department)
WHEN Colmn = 'Dlocation' THEN
(SELECT RTRIM(XMLAGG(XMLELEMENT(e, Dlocation || ' ')).EXTRACT ('//text()')) FROM Department)
END ChrData
FROM (SELECT TabColmn.Colmn
FROM (SELECT 'DeptId' AS Colmn
FROM dual
UNION ALL
SELECT 'Dname' AS Colmn FROM dual
UNION ALL
SELECT 'Dlocation' AS Colmn FROM dual
) TabColmn
)

VIJAY SHARMA

• Sep 24th, 2012

 

SELECT SYS_CONNECT_BY_PATH(DEPTNO, )I ,
SYS_CONNECT_BY_PATH(DNAME, ) II,
SYS_CONNECT_BY_PATH(LOC, ) III
FROM DEPT
WHERE LEVEL = &TOTALDEPARTMENTS
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO < DEPTNO;

srini

 Profile Answers by srini 

• Dec 6th, 2013

 

Using PIVOT we can do this:

http://www.dba-oracle.com/t_pivot_examples.htm