Search code examples
sqlsnowflake-cloud-data-platformansi-sql

How to assign multiple dates to multiple categories in SQL?

I have a query which will return 3 dates.

The query is

SELECT DISTINCT date FROM date_table

Output is

date
2020-02-10
2019-08-08
2017-09-06
2021-11-10

I have another table called sales with only category in it.

Category
chocs
biscuits

I'm trying to assign those above 4 dates to every category. Like below data

category    date        
chocs       2020-02-10 
chocs       2019-08-08  
chocs       2017-09-06 
chocs       2021-11-10 
biscuits    2020-02-10
biscuits    2019-08-08
biscuits    2017-09-06
biscuits    2021-11-10

How to write a SQL query for this?

Solution

  • Use a cross join:

    SELECT s.category, d.date
FROM sales s
CROSS JOIN date_table d
ORDER BY s.category, d.date;