title: sql cross join - what use has anyone found for it?
body: <p>Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use has anyone found in production code for the cross join?     </p>
  
  <p>Update: the code posted by Tony Andrews is very close to what I used the cross join for. Believe me, I understand the implications of using a cross join and would not do so lightly. I was excited to have finally used it (I'm such a nerd) - sort of like the time I first used a full outer join.    </p>
  
  <p>Thanks to everyone for the answers! Here's how I used the cross join:</p>
  
  <pre><code>SELECT  CLASS, [Trans-Date] as Trans_Date,
  SUM(CASE TRANS
       WHEN 'SCR' THEN [Std-Labor-Value]
       WHEN 'S+' THEN [Std-Labor-Value]
       WHEN 'S-' THEN [Std-Labor-Value]
       WHEN 'SAL' THEN [Std-Labor-Value]
       WHEN 'OUT' THEN [Std-Labor-Value]
       ELSE 0
  END) AS [LABOR SCRAP],
  SUM(CASE TRANS
       WHEN 'SCR' THEN  [Std-Material-Value]
       WHEN 'S+' THEN [Std-Material-Value]
       WHEN 'S-' THEN  [Std-Material-Value]
       WHEN 'SAL' THEN [Std-Material-Value]
       ELSE 0
  END) AS [MATERIAL SCRAP], 
  SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR REWORK],
  SUM(CASE TRANS 
       WHEN 'PRD' THEN  [Act-Labor-Value]
       WHEN 'TRN' THEN  [Act-Labor-Value]
       WHEN 'RWK' THEN  [Act-Labor-Value]
       ELSE 0 
  END) AS [ACTUAL LABOR],
  SUM(CASE TRANS 
       WHEN 'PRD' THEN  [Std-Labor-Value]
       WHEN 'TRN' THEN   [Std-Labor-Value]
       ELSE 0 
  END) AS [STANDARD LABOR],
  SUM(CASE TRANS 
       WHEN 'PRD' THEN  [Act-Labor-Value] - [Std-Labor-Value]
       WHEN 'TRN' THEN  [Act-Labor-Value] - [Std-Labor-Value]
       --WHEN 'RWK' THEN  [Act-Labor-Value]
       ELSE 0 END) -- - SUM([Std-Labor-Value]) -- - SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) 
  AS [LABOR VARIANCE] 
  FROM         v_Labor_Dist_Detail
  where [Trans-Date] between @startdate and @enddate
      --and CLASS = (CASE @class WHEN '~ALL' THEN CLASS ELSE @class END)
  GROUP BY  [Trans-Date], CLASS
  UNION  --REL 2/6/09 Pad result set with any missing dates for each class. 
  select distinct [Description] as class,  cast([Date] as datetime) as [Trans-Date], 0,0,0,0,0,0 
  FROM Calendar_To_Fiscal cross join PRMS.Product_Class
  where cast([Date] as datetime) between @startdate and @enddate and
  not exists (select class FROM v_Labor_Dist_Detail vl where [Trans-Date] between @startdate and @enddate
                      and vl.[Trans-Date] = cast(Calendar_To_Fiscal.[Date] as datetime)
                      and vl.class= PRMS.Product_Class.[Description]
                  GROUP BY [Trans-Date], CLASS)
  order by [Trans-Date], CLASS
  </code></pre>