Oracle 11g: How to union an oracle table with itself?

The query could look like this:

with matches as (   select "DATE", code, name,      max(case when "SET" = 1 then score end) score_1,      max(case when "SET" = 2 then score end) score_2,      max(case when "SET" = 3 then score end) score_3,      row_number() over(partition by "DATE", code order by name) team_no    from games    group by "DATE", code, name)select a."DATE", a.code, a.name || ' vs. ' || b.name teams,  a.score_1 || '-' || b.score_1 set_1,  a.score_2 || '-' || b.score_2 set_2,  a.score_3 || '-' || b.score_3 set_3from matches ajoin matches b on a."DATE" = b."DATE" and a.code = b.codewhere a.team_no = 1 and b.team_no = 2;

date and set are rather unfortunate column names.

The query works in 3 steps:

1. The records are aggregated to create a single row per team and match. In that process, the score is assigned to one of the three columns set_1, set_2, set_3.
2. Row numbers are assigned to each row, starting at 1 for each match. The result is that one team is assigned 1 and the other one is assigned 2 (column team_no).
3. The resulting table is joined to itself, the left side for teams with no. 1 and the right side for teams with no. 2 using the match (date and code) as the join condition. The result is one row per match with the names and scores of both teams.

First, group the data by "date", code, "set" to LISTAGG the teams and the scores. Then pivot the results on the scores column. Here's the SQL for it:

WITH grouped AS (  SELECT    "date", code, "set",    LISTAGG(name,  ' VS. ') WITHIN GROUP (ORDER BY name) AS teams,    LISTAGG(score, '-'    ) WITHIN GROUP (ORDER BY name) AS score  FROM matches  GROUP BY    "date", code, "set"),    pivoted AS (  SELECT    "date", code, teams,    nvl("1", '-') AS set1,    nvl("2", '-') AS set2,    nvl("3", '-') AS set3  FROM grouped  PIVOT (    MAX(score) FOR "set" IN (1, 2, 3)  ) p)SELECT * FROM pivoted;

Please have a look at this query at SQL Fiddle too.

I'd do sg like this, if the name of the table is let's say VOLLEYBALL:

SELECT temp.date, temp.code,     temp.team1 || ' vs. ' || temp.team2 AS teams,     (SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team1 AND v.set = 1) || '-' ||         (SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team2 AND v.set = 1) AS set1,     (SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team1 AND v.set = 2) || '-' ||         (SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team2 AND v.set = 2) AS set2,     nvl((SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team1 AND v.set = 3) || '-' ||         (SELECT v.score FROM volleyball v WHERE v.code = temp.code AND v.name = team2 AND v.set = 3)        , '-') AS set3 -- optional, if no results, then it will be a '-' FROM    (SELECT v.date, v.code,         min(v.name) AS team1,  max(v.name) AS team2     FROM volleyball v     GROUP BY v.date, v.code) temp; 

This will result a one row summary.