# Fastest way to get a intrinsic cartesian product of grouped data

Consider table:

``````CREATE TABLE dbo.DIMENSIONS (
dim_order int,
dim_name varchar(50),
member_key varchar(50)
)

SELECT * FROM dbo.DIMENSIONS

dim_order   dim_name     member_key
----------- ------------ ------------------------------
1           ENTITY         BL23A2
1           ENTITY         MBL23C8
2           YEAR           2021
2           YEAR           2022
2           YEAR           2023
3           MONTH          JAN
3           MONTH          FEB
``````

The goal is to get the cartesian product of groups:

``````ENTITY         BL23A2
ENTITY         MBL23C8
``````
``````YEAR           2021
YEAR           2022
YEAR           2023
``````
``````MONTH          JAN
MONTH          FEB
``````

with respect to `dim_order` as final column order. Expected result:

``````ID DIM1     DIM2      DIM3
-- -------- --------- ----------
1  BL23A2   2021      JAN
2  BL23A2   2021      FEB
3  BL23A2   2022      JAN
4  BL23A2   2022      FEB
...      ...       ...
12 MBL23C8  2023      FEB
``````

The solution should not use dynamic SQL. It can be assumed that the `dim_name` column contains only values: `ENTITY,YEAR,MONTH`. The explanation why the solution is optimal would be appreciated.

• Can you please provide the schema and some sample data for `dim_order`?
– J.D.
Jun 2, 2023 at 12:39

Edit: shoot, I didn't notice it's for SQL Server. *SWEAT*
Edit: No idea if they have cross join. *SWEAT*
Edit: If someone wants to salvage my answer, feel free to do it.

For starter, I think you are mixing oranges with apples with entities and dates in the same table. It's an antipattern I unfortunately don't remember the name.

Your table is not normalized as you repeat again and again that ENTITY is dim_order 1 twice, that YEAR is dim_order 2 three times, etc. If you only have ENTITY, YEAR and MONTH, I am sure you can do the ordering just fine without this column, either application-wise, or by selecting your columns in the right order?

Now, I have no idea what you are trying to accomplish but I would probably have three tables: "entity", "year" and "month". Year and month being of the same nature (date), I don't know why you are doing this instead of storing directly a cartesian product of year and month in some "date" table.

About the cartesian product, I present you CROSS JOIN:

T1 CROSS JOIN T2

For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows.

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

``````SELECT ROW_NUMBER() OVER (ORDER BY entity.name) AS id, entity.name, year, month
FROM entity, year, month;
``````

Very simple.

All that is left for you is to self join on your table and you will see how cumbersome it is when you mix up oranges with apples.

``````SELECT ROW_NUMBER() OVER (ORDER BY d1.member_key) AS id, d1.member_key, d2.member_key, d3.member_key FROM
(SELECT member_key FROM dimensions where dim_order = 1) d1,
(SELECT member_key FROM dimensions where dim_order = 2) d2,
(SELECT member_key FROM dimensions where dim_order = 3) d3
``````

Is this the most efficient way to do that? No idea as I am not an expert.

• Table `dbo.DIMENSION` has its structure to maintain question simplicity. In the database dimensions are of course stored in another way. It doesn't matter what dimensions are in the table, whether it is year, month or scenario. Jun 2, 2023 at 11:59
• The answear is wrong, because it assumes that `ENTITY` is first dimensions, `YEAR` second and `MONTH` third. It shouldn't be hard-coded. I would imagine that the solution uses `GROUP BY dim_name` and then perform cartesian product operation on groups. Jun 2, 2023 at 12:04
• You said "It can be assumed that the dim_name column contains only values: ENTITY,YEAR,MONTH." so I didn't see a problem with hardcoding so few values as it made more sense than relying on an extra redundant column to decide the order. Then it's just a matter to select based on dim_order instead (it's identical) but you are going to tell me I can't hardcode the numbers, aren't you? Jun 2, 2023 at 12:39
• @AKedzierski: "I would imagine that the solution uses `GROUP BY dim_name` and then perform cartesian product operation on groups." – that would be imagining it wrongly. If you group your table by `dim_name`, you will get a dataset consisting of no more than three rows (since we are to assume that `dim_name` can have only three possible values). Grouping in SQL doesn't just mean "putting rows together in a group", it means aggregating them, so that some rows (those defined as grouping criteria) can be referenced directly while others (non-GROUP BY columns) only via an aggregate function. Jun 5, 2023 at 21:03
• @AKedzierski: In this case, you want a cross product of three subsets of the source table. In order to get a cross product of sets in SQL, you cross-join them. This is exactly what this solution does. It retrieves the three subsets and cross-joins them (albeit using the old-fashion comma join rather than `CROSS JOIN`, but either does the job). Jun 5, 2023 at 21:08