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.


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top