In SQL, what's the best way to convert a single value to multiple rows of output?

Jim Burnell 0
Viewed 59

This should be an easy one.

I have a table with rows:

CREATE TABLE FURNITURE
       (ROOM VARCHAR2(50) CONSTRAINT PK_DEPT PRIMARY KEY,
        APPLIANCE VARCHAR2(50));
INSERT INTO FURNITURE VALUES
        ('BEDROOM','BED');
INSERT INTO FURNITURE VALUES
        ('LIVING ROOM', 'SOFA');
INSERT INTO FURNITURE VALUES
        ('TV ROOM', 'TV');
INSERT INTO FURNITURE VALUES
        ('ALL', 'LAMP');

The desired output is:

Room Appliance
BEDROOM BED
BEDROOM LAMP
LIVING ROOM SOFA
LIVING ROOM LAMP
TV ROOM TV
TV ROOM LAMP

(sorting doesn't matter).

I have one very naive way of doing it:

select f1.room,
       f1.appliance
  from furniture f1 where room <> 'ALL'
 union
select all_rooms.room,
       f2.appliance
  from furniture f2
cross join
       (select distinct room from furniture where room <> 'ALL') all_rooms
 where f2.room = 'ALL';

Surely there's a better way?

I was trying to come up with a solution using CROSS APPLY but I'm failing.

SQL Fiddle at http://sqlfiddle.com/#!4/0db734/10

oracle sql
3 Answers

Use a self-join:

SELECT f1.ROOM, f2.APPLIANCE
FROM FURNITURE f1 INNER JOIN FURNITURE f2
ON (f2.ROOM = f1.ROOM AND f2.APPLIANCE = f1.APPLIANCE) OR f2.ROOM = 'ALL'
WHERE f1.ROOM <> 'ALL';

See the demo.

forpas0

You can find all the rooms and then use an INNER JOIN to the furniture:

SELECT r.room,
       f.appliance
FROM   (SELECT room FROM furniture WHERE room <> 'ALL') r
       INNER JOIN furniture f
       ON (r.room = f.room OR f.room = 'ALL')

Which, for the sample data, outputs:

ROOM APPLIANCE
BEDROOM LAMP
LIVING ROOM LAMP
TV ROOM LAMP
BEDROOM BED
LIVING ROOM SOFA
TV ROOM TV

fiddle

MT00

I finally got CROSS APPLY to work...although it doesn't work in the fiddle, because CROSS APPLY isn't available until Oracle 12:

SELECT all_rooms.room,
       f1.appliance
  FROM furniture f1
 CROSS apply (SELECT DISTINCT f2.room
                FROM furniture f2
               WHERE f2.room <> 'ALL'
                 AND (f1.room = f2.room OR f1.room = 'ALL')) all_rooms;
Jim Burnell0
Related