SQL - Question is been asked in the interview., I have table Department with details likeDeptId Dname

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 150 of 171

Question is been asked in the interview., I have table Department with details like

DeptId Dname Dlocation
10 Finance Del
20 Sales Mum
30 Marketing Blore

The output should be in this format

10 20 30
Finance Sales Marketing
Del Mum Blore

The query I need in SQL not using any transformation.

Can anyone help me out in this ?

Total Answers and Comments: 3

Last Update: March 12, 2008 Asked by: hamsa

Sponsored Links

Best Rated Answer

—

Submitted by: arun_doss

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

Above answer was rated as good by the following members:
spohilko

February 18, 2008 08:56:46

ABHIDHAN

Member Since: October 2007 Contribution: 4

RE: 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

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

Is this answer useful? Yes | No

February 26, 2008 05:26:58

sampra

Member Since: February 2008 Contribution: 278

RE: 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

nice question giv some good answer

Is this answer useful? Yes | No

March 12, 2008 11:59:42

arun_doss

Member Since: March 2008 Contribution: 2

RE: 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

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

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank