table1.id1 table2.id2 111 1000 111 1001 111 1002 222 1003 222 1002 222 1005I 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 1005The 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 1005I 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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in August 2002