title: In SQL, what's the best way to convert a single value to multiple rows of output?
body: <p>This should be an easy one.</p>
  <p>I have a table with rows:</p>
  <pre><code>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');
  </code></pre>
  <p>The desired output is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Room</th>
  <th>Appliance</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>BEDROOM</td>
  <td>BED</td>
  </tr>
  <tr>
  <td>BEDROOM</td>
  <td>LAMP</td>
  </tr>
  <tr>
  <td>LIVING ROOM</td>
  <td>SOFA</td>
  </tr>
  <tr>
  <td>LIVING ROOM</td>
  <td>LAMP</td>
  </tr>
  <tr>
  <td>TV ROOM</td>
  <td>TV</td>
  </tr>
  <tr>
  <td>TV ROOM</td>
  <td>LAMP</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>(sorting doesn't matter).</p>
  <p>I have one very naive way of doing it:</p>
  <pre><code>select f1.room,
         f1.appliance
    from furniture f1 where room &lt;&gt; 'ALL'
   union
  select all_rooms.room,
         f2.appliance
    from furniture f2
  cross join
         (select distinct room from furniture where room &lt;&gt; 'ALL') all_rooms
   where f2.room = 'ALL';
  </code></pre>
  <p>Surely there's a better way?</p>
  <p>I was trying to come up with a solution using CROSS APPLY but I'm failing.</p>
  <p>SQL Fiddle at <a href="http://sqlfiddle.com/#!4/0db734/10" rel="nofollow noreferrer">http://sqlfiddle.com/#!4/0db734/10</a></p>