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
col1 col2 col3 col4 111 1000 1001 1002 222 1003 1002 1005
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);
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;
ID1 First Second Third --- ----- ------ ----- 111 1000 1001 1002 222 1002 1003 1005
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.