Q

Displaying results horizontally

I'm working with Oracle. I have two tables with a m*n relation between them. The intermediate table between both of them is like this:

table1.id1      table2.id2
111               1000
111               1001
111               1002
222               1003
222               1002
222               1005
I hope you can tell me how to make a select to visualize the values like this:
col1    col2  col3 col4
111     1000  1001 1002
222     1003  1002 1005
The thing is, that not all the id of the table1 are related with the same number of id's in the table2. The maximum related is 3.


SQL was never designed for creative formatting and presentation of data; reporting and presentation tools tend to do a much better job. However, questions regarding cross tabulation using SQL continue to surface in forums such as this. This particular question caught my attention in that it dealt with slightly unique criteria: There would be a known maximum number of detail columns (three), and we wouldn't know the values assigned to each column beforehand.

Let's start with a sample table and its data:

create table table1
( id1 numeric(3),
  id2 numeric(4) );

insert into table1 values (111,1000);
insert into table1 values (111,1001);
insert into table1 values (111,1002);
insert into table1 values (222,1003);
insert into table1 values (222,1002);
insert into table1 values (222,1005);
As with most SQL problems, there are several ways we can solve this one. The solution I present here involves obtaining the value where only one value is less than or equal to it (itself) for the first column, two values are less than or equal to it for the second column, etc... This has the possibly fortunate side-affect of sorting them.
select id1, 
       ( select id2 from table1 y 
           where id1 = x.id1 and 1 = ( select count(*) from table1 
                                         where id1 = x.id1 and id2 <= y.id2 ) 
       ) as "First",
       ( select id2 from table1 y 
           where id1 = x.id1 and 2 = ( select count(*) from table1 
                                         where id1 = x.id1 and id2 <= y.id2 ) 
       ) as "Second",
       ( select id2 from table1 y 
           where id1 = x.id1 and 3 = ( select count(*) from table1 
                                         where id1 = x.id1 and id2 <= y.id2 ) 
       ) as "Third"
  from ( select distinct id1 from table1) x;
Here are the results:
ID1 First Second Third
--- ----- ------ -----
111  1000   1001  1002
222  1002   1003  1005
I should note that this will not work in cases where there are duplicates. If that were the case, however, we could simply take counts of distinct values.

For More Information


This was first published in August 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close