pivoting tables into columns

From: Alexander Birch <abirch_at_gmail.com>
Date: 30 Jun 2006 15:34:42 -0700
Message-ID: <1151706882.115058.270540@d30g2000cwa.googlegroups.com>

What would be the best way to approach the following problem? Would it be to use sql to obtain the data, then manipulate it with a procedural lanugage? Or would is it possible to do this with pure sql.

there are three tables
test1, test2, test3

in each of these tables I have different samples sample1, sample2, sample3, sample4

in test1 I have the following

---

sample1 result1a
sample1 result1b
sample2 result1a
sample4 result1a

in test2
sample1 result2a
sample2 result2a
sample2 result2b
sample3 result2a

in test3
sample1 result3a
sample3 result3a
sample3 result3b
sample3 result3c

is there anyway in sql (including pl/sql) to do the following

sample | Test1 | Test2 | Test3
----------+------------+------------+-----------
sample1 | result1a | result2a | result3a
----------+------------+------------+-----------
sample1 | result1b | |
----------+------------+------------+-----------
sample2 | result1a | result2a |
----------+------------+------------+-----------
sample2 | | result2b |
----------+------------+------------+-----------
sample3 | | result2a | result3a
----------+------------+------------+-----------
sample3 | | | result3b
----------+------------+------------+-----------
sample3 | | | result3c
----------+------------+------------+-----------
sample4 | result1a | |
----------+------------+------------+-----------
Received on Fri Jun 30 2006 - 17:34:42 CDT