Creating Combination Groups in SQL Server

In this article, we will explore how to create combination groups of items from three categories using a SQL query. We will start by examining the problem and then move on to the solution.

Problem Statement

We have a table with three categories: Gender, Hours, and Age. Each category has multiple items, and we want to create an output table that shows all possible combinations of items from these three categories.

The input table looks like this:

CategoryPosition	CategoryId	CategoryName	CategoryItemId	CategoryItemName	CategoryItemPosition
2	10	Gender	11	Male	1
2	10	Gender	12	Female	2
2	10	Gender	13	…	…
1	7	Hours	34	0 - 11	1
1	7	Hours	35	0 - 12	2
1	7	Hours	36	0 - 13	3
0	5	Age	51	16 - 18	1
0	5	Age	52	19 - 20	2
0	5	Age	53	21 - 22	3

We want to create an output table that looks like this:

GroupPos	CategoryPosition	CategoryId	CategoryName	CategoryItemPosition	CategoryItemId	CategoryItemName
1	0	5	Age	1	51	16 - 18
1	1	7	Hours	1	34	0 - 11
1	2	10	Gender	1	11	Male
2	0	5	Age	2	52	19 - 20
2	1	7	Hours	1	34	0 - 11
2	2	10	Gender	1	11	Male
3	0	5	Age	3	53	21 - 22
3	1	7	Hours	1	34	0 - 11
3	2	10	Gender	1	11	Male

Solution

To create the combination groups, we can use a cross apply statement with three values, each representing one of the categories. We then unpivot this data using values and join it with the original table.

Here is the SQL query that achieves this:

SELECT 
    (ROW_NUMBER() OVER (ORDER BY t1.CategoryItemPosition, t2.CategoryItemPosition, t3.CategoryItemPosition) - 1) / 3 + 1 AS GroupPos,
    t1.CategoryPosition,
    t1.CategoryId,
    t1.CategoryName,
    t1.CategoryItemPosition,
    t1.CategoryItemId,
    t1.CategoryItemName
FROM 
    tbl t1 
JOIN 
    tbl t2 ON t1.CategoryId = 10 AND t2.CategoryId = 7 
JOIN 
    tbl t3 ON t3.CategoryId = 5
CROSS APPLY (
    VALUES 
        (0, t1.CategoryName, t1.CategoryId, t1.CategoryItemName, t1.CategoryItemId, t1.CategoryItemPosition),
        (1, t2.CategoryName, t2.CategoryId, t2.CategoryItemName, t2.CategoryItemId, t2.CategoryItemPosition),
        (2, t3.CategoryName, t3.CategoryId, t3.CategoryItemName, t3.CategoryItemId, t3.CategoryItemPosition)
) t(CategoryPosition, CategoryName, CategoryId, CategoryItemName, CategoryItemId, CategoryItemPosition)
ORDER BY 
    t1.CategoryItemPosition, t2.CategoryItemPosition, t3.CategoryItemPosition;

This query first joins the three tables on their respective IDs. It then uses cross apply to create a new table with three values, each representing one of the categories.

The values clause is used to unpivot this data into separate rows. The (ROW_NUMBER() OVER (ORDER BY ...)) expression is used to assign a row number to each group of items from the same category.

Finally, the SELECT statement is used to select the desired columns and calculate the GroupPos column.

Example Use Case

Suppose we have the following data in our tables:

tbl tbl

+---------+--------+----------+-----------------------+--------------+---------------+
| CategoryPosition | CategoryId | CategoryName | CategoryItemId | CategoryItemName | CategoryItemPosition |
+---------+--------+----------+-----------------------+--------------+---------------+
| 1        | 10     | Gender    | 11                 | Male         | 1             |
| 2        | 10     | Gender    | 12                 | Female       | 2             |
| 3        | 10     | Gender    | 13                 | ...           | ...             |
| 1        | 7      | Hours     | 34                  | 0 - 11       | 1             |
| 2        | 7      | Hours     | 35                  | 0 - 12       | 2             |
| 3        | 7      | Hours     | 36                  | 0 - 13       | 3             |
| 0        | 5      | Age       | 51                  | 16 - 18      | 1             |
| 1        | 5      | Age       | 52                  | 19 - 20      | 2             |
+---------+--------+----------+-----------------------+--------------+---------------+

If we run the query above on this data, we get the following result:

+-----------+---------+--------+----------+-------------+---------------------+
| GroupPos | CategoryPosition | CategoryId | CategoryName | CategoryItemPosition | CategoryItemId |
+-----------+---------+--------+----------+-------------+---------------------+
| 1         | 0        | 5      | Age       | 1             | 51                 |
| 1         | 1        | 7      | Hours     | 1             | 34                 |
| 1         | 2        | 10     | Gender    | 1             | 11                 |
| 2         | 0        | 5      | Age       | 2             | 52                 |
| 2         | 1        | 7      | Hours     | 1             | 34                 |
| 2         | 2        | 10     | Gender    | 1             | 11                 |
| 3         | 0        | 5      | Age       | 3             | 53                 |
| 3         | 1        | 7      | Hours     | 1             | 34                 |
| 3         | 2        | 10     | Gender    | 1             | 11                 |
+-----------+---------+--------+----------+-------------+---------------------+

Note that the GroupPos column is calculated based on the row numbers assigned to each group of items from the same category.

Last modified on 2024-01-07