title: Sql - alternative to WITH ... AS
body: <p>Is there an <strong>efficient</strong> alternative SQL to this example? I do not want to use WITH ... AS, that is the main criteria</p>
  
  <pre><code>WITH TEMP_TABLE AS (
       SELECT status, COUNT(1) as total FROM XYZ GROUP BY status
      )
  SELECT 
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'A'),0) AS A_COUNT ,
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'B'),0) AS B_COUNT ,
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'C'),0) AS C_COUNT ,
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'D'),0) AS D_COUNT , 
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'E'),0) AS E_COUNT ,
           NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'F'),0) AS F_COUNT  
  FROM DUAL; 
  </code></pre>
  
  <p>I read similar questions but they use other ddl stuff, I just want a basic SQL statement.</p>