Question is been asked in the interview., I have table Department with details likeDeptId Dname Dlocation10 Finance Del20 Sales Mum30 Marketing BloreThe output should be in this format 10 20 30 Finance Sales Marketing Del Mum BloreThe query I need in SQL not using any transformation.Can anyone help me out in this ?

Questions by hamsa

Showing Answers 1 - 7 of 7 Answers

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......

  Was this answer useful?  Yes

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

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

  1.          WHEN Colmn = 'DeptId' THEN

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

  3.          WHEN Colmn = 'Dname' THEN

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

  5.          WHEN Colmn = 'Dlocation' THEN

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

  7.        END ChrData

  8. FROM (SELECT TabColmn.Colmn

  9.             FROM (SELECT 'DeptId' AS Colmn FROM dual

  10.                        UNION ALL

  11.                       SELECT 'Dname' AS Colmn FROM dual

  12.                        UNION ALL

  13.                       SELECT 'Dlocation' AS Colmn FROM dual

  14.           ) TabColmn

  15.       )

  Was this answer useful?  Yes

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;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions