scissorhand
asked on
Display Data In One Row Based On Similar Criteria
For example, I have data as below:-
ID Name Category
123 John Professional
102 Mary Amateur
111 Candy Amateur
542 Steven Professional
451 Mariah Professional
423 Kane Intermediate
How do I display the result based on the category as below:-
ID Name ID Name ID Name Category
123 John 542 Steven 451 Mariah Professional
102 Mary 111 Candy Amateur
423 Kane Intermediate
Is it possible to use only sql statement to display the result without using PL/SQL?
Thanx
ID Name Category
123 John Professional
102 Mary Amateur
111 Candy Amateur
542 Steven Professional
451 Mariah Professional
423 Kane Intermediate
How do I display the result based on the category as below:-
ID Name ID Name ID Name Category
123 John 542 Steven 451 Mariah Professional
102 Mary 111 Candy Amateur
423 Kane Intermediate
Is it possible to use only sql statement to display the result without using PL/SQL?
Thanx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what if we don't know about the maximum number of item? The number of item may be more than 10, is there a dynamic way to display the result and make the statement shorter?
scissorhand,
A relational table by its very definition has a fixed known-beforehand number of columns. If you want to perform a dynamic pivot, it means that the data model is probably not well-thought...
Anyway, yes, there is a way to perform a dynamic pivot.
1. You need to determine the maximum number of columns using someting like (in PL/SQL):
select max(count(*)) from t1 group by category
2. Build a dynamic SQL using the pattern above
3. execute it.
There is a detailed example on how to do exactly that in the book "expert one-on-one Oracle" by Tom Kyte .
VC
A relational table by its very definition has a fixed known-beforehand number of columns. If you want to perform a dynamic pivot, it means that the data model is probably not well-thought...
Anyway, yes, there is a way to perform a dynamic pivot.
1. You need to determine the maximum number of columns using someting like (in PL/SQL):
select max(count(*)) from t1 group by category
2. Build a dynamic SQL using the pattern above
3. execute it.
There is a detailed example on how to do exactly that in the book "expert one-on-one Oracle" by Tom Kyte .
VC
Sample would be:
select
case
when deptno=10 then empno
end as EMP_10,
case
when deptno=10 then ename
end as ENAME_10,
case
when deptno=20 then empno
end as EMP_20,
case
when deptno=20 then ename
end as ENAME_20,
case
when deptno=30 then empno
end as EMP_30,
case
when deptno=30 then ename
end as ENAME_30,
deptno
from emp