sql cross join - what use has anyone found for it? [closed]

Question

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?

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.

Thanks to everyone for the answers! Here's how I used the cross join:

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

Answer 1

A typical legitimate use of a cross join would be a report that shows e.g. total sales by product and region. If no sales were made of product P in region R then we want to see a row with a zero, rather than just not showing a row.

select r.region_name, p.product_name, sum(s.sales_amount)
from regions r
cross join products p
left outer join sales s on  s.region_id = r.region_id
                        and s.product_id = p.product_id
group by r.region_name, p.product_name
order by r.region_name, p.product_name;

Answer 2

One use I've come across a lot is splitting records out into several records, mainly for reporting purposes.

Imagine a string where each character represents some event during the corresponding hour.

ID | Hourly Event Data
1  | -----X-------X-------X--
2  | ---X-----X------X-------
3  | -----X---X--X-----------
4  | ----------------X--X-X--
5  | ---X--------X-------X---
6  | -------X-------X-----X--

Now you want a report which shows how many events happened at what day. Cross join the table with a table of IDs 1 to 24, then work your magic...

SELECT
   [hour].id,
   SUM(CASE WHEN SUBSTRING([data].string, [hour].id, 1) = 'X' THEN 1 ELSE 0 END)
FROM
   [data]
CROSS JOIN
   [hours]
GROUP BY
   [hours].id

=>

1,  0
2,  0
3,  0
4,  2
5,  0
6,  2
7,  0
8,  1
9,  0
10, 2
11, 0
12, 0
13, 2
14, 1
15, 0
16, 1
17, 2
18, 0
19, 0
20, 1
21, 1
22, 3
23, 0
24, 0