Transpose select results with Oracle

Question

Hi All,

my question is, with some background:

I have to generate some sql queries based on the table metadata (column format), and the result is something like:

TABLENAME1|COL1
TABLENAME1|COL2
TABLENAME2|COL1
TABLENAME2|COL2
TABLENAME2|COL3
TABLENAME3|COL1
TABLENAME4|COL1
TABLENAME4|COL2
... /*some other 1800 rows */

(Yeah, it's ordered.) What I need is to transpose this data, based on the first column, so the expected output would be:

TABLENAME1|COL1|COL2|NULL
TABLENAME2|COL1|COL2|COL3
TABLENAME3|COL1|NULL|NULL
TABLENAME4|COL1|COL2|NULL
/* less then 1800 rows ;-) */

Is it possible using Oracle SQL?

Thanks in advance!

Answer 1

The keyword that you're looking for is pivot. Here's an example of its use. The syntax appears generally the same as MSSQL, and since I don't know the url for Oracle's documentation I'll toss up the MSDN link and hope that someone that knows more about Oracle's page layout than me will edit it to point to their documentation.

EDIT: Oracle documentation, search for pivot enough times and you'll get to the details.

Answer 2

If you want to generate the query for each call or use a hardcoded max-column-count, then you can do something like that:

WITH tab AS
(
  SELECT table_name, column_name FROM user_tab_cols WHERE column_id <= 4
) -- user_tab_cols used to provide test data, use your table instead
SELECT MAX(c1) c1,
       MAX(c2) c2,
       MAX(c3) c3,
       MAX(c4) c4
  FROM (SELECT table_name,
               DECODE( column_id, 1, column_name ) c1,
               DECODE( column_id, 2, column_name ) c2,
               DECODE( column_id, 3, column_name ) c3,
               DECODE( column_id, 4, column_name ) c4
          FROM ( SELECT table_name,
                        column_name,
                        ROW_NUMBER() OVER ( PARTITION BY table_name ORDER BY column_name ) column_id
                   FROM tab
               )
       )
 GROUP BY table_name
 ORDER BY table_name

---

If it is sufficient to get it in that form

TABLENAME1|COL1,COL2
TABLENAME2|COL1,COL2,COL3

have a look at Tom Kyte's stragg.