oracle cross join on same table

Question

I have a table in Oracle database and it has lot of records. That table has a column "type" that specifies type of each record. I wanted to do cross join records of different types.

Assume this table "RecordTypes"

Name    Type    Group
1name1  1   1
1name2  1   1
2name1  2   1
2name2  2   2
3name1  3   3
3name2  3   3

I want a query to cross join on the rows of this table based on Type column. In this table there are 3 types of records based on "Type" column. The query should produce this result.

Type1   Type2   Type3
1name1  2name1  3name1
1name1  2name1  3name2
1name1  2name2  3name1
1name1  2name2  3name2
1name2  2name1  3name1
1name2  2name1  3name2
1name2  2name2  3name1
1name2  2name2  3name2

I can produce this result based on this query, but I have hardcoded the type value. In realtime, I don’t know what and how many distinct values will be there for "Type" column.

With type1 as (select name from table where type = 1),
Type2 as (select name from table where type = 2),
Type3 as (select name from table where type = 3)
Select * from type1, type2, type3.

I want a query/stored procedure to return the result where the stored proc should find the distinct type value available and return the result as above. Also, from the result I need to remove the records when 2 or more columns in a row belong to same "Group".

It's a basic rule that you can always tell how many columns a query will output (and their names and types) by looking at the query itself, without knowing what data is in the tables. So if you want a query that works like your example, but would output an extra "Type4" column after a record with Type=4 was inserted into the table, you're out of luck. Such a thing would be called a "dynamic pivot", and lots of people have looked for it ([example](http://stackoverflow.com/q/15491661/2404501)), but it doesn't exist. — , May 06 '15 at 16:48
Unfortunately, the requirement i have is not with the basic rule that you can always tell how many columns a query will output (and their names and types) . — Karthick, May 07 '15 at 16:22

score 0 · Accepted Answer · edited Jul 07 '19 at 09:46

You could build a query dynamically similar to the following:

CREATE OR REPLACE FUNCTION SOME_PROC()
  RETURNS VARCHAR2
IS
  strQuery   VARCHAR2(32767) := 'WITH ';
  strTables  VARCHAR2(32767);
  c          SYSREFCURSOR;
BEGIN
  FOR aRow IN (SELECT DISTINCT TYPE FROM RECORDTYPES)
  LOOP
    strQuery := strQuery || 'TYPE' || aRow.TYPE ||
                ' AS (SELECT NAME FROM SOME_TABLE WHERE TYPE = ' ||
                aRow.TYPE || '),';

    strTables := strTables || 'TYPE' || aRow.TYPE || ',';
  END LOOP;

  strQuery := RTRIM(strQuery, ',') ||  -- remove trailing comma
              ' SELECT * FROM ' ||
              RTRIM(strTables, ',');   -- remove trailing comma

  RETURN strQuery;
END SOME_PROC;

To use, call this function to obtain the text of the query, then the calling code can execute the query and do whatever's necessary to process it.

All purely off the top of my head, not tested on animals, you'll be first! :-)

Thank you Bob Jarvis. This really would help. – Karthick May 07 '15 at 16:09 — Karthick, May 07 '15 at 16:09

oracle cross join on same table

1 Answers1