How to create combination groups in sql server-CodePudding

I have a given table as input:

CategoryPosition	CategoryId	CategoryName	CategoryItemId	CategoryItemName	CategoryItemPosition
2	10	Gender	11	Male	1
2	10	Gender	12	Female	2
2	10	Gender	13	N/A	3
1	7	Hours	34	0 - 11	1
1	7	Hours	35	12 - 39	2
1	7	Hours	36	40 - 79	3
1	7	Hours	37	80 - 119	4
1	7	Hours	38	120	5
0	5	Age	51	16-18	1
0	5	Age	52	19-24	2
0	5	Age	53	25-44	3
0	5	Age	54	45-54	4
0	5	Age	55	55-59	5
0	5	Age	56	60	6

Total number of rows : 3 (number of items from Gender ) 5 ( number of items from Hours) 6 (number of items from Age) = 14

What I need to produce is an output table using combinations groups of items from the 3 categories like below.

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-24
2	1	7	Hours	1	34	0 - 11
2	2	10	Gender	1	11	Male
3	0	5	Age	3	53	25-44
3	1	7	Hours	1	34	0 - 11
3	2	10	Gender	1	11	Male

......

Total number of rows : 3 (number of items from Gender ) * 5 ( number of items from Hours) * 6 (number of items from Age) * 3 (number of categories) = 270

CodePudding user response：

You want kind of cross join plus unpivot

select (row_number() over(order by t1.CategoryItemPosition, t2.CategoryItemPosition, t3.CategoryItemPosition) - 1) / 3   1 GroupPos, t.*
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

CodePudding user response：

select the different attributes as if dealing with different tables:

with g as (select categoryitemname as gender from mytable where categoryname = 'Gender')
   , h as (select categoryitemname as hours from mytable where categoryname = 'Hours')
   , a as (select categoryitemname as age from mytable where categoryname = 'Age')
select *
from g cross join h cross join a
order by g.gender, h.hours, a.age;

Add more columns to the subqueries, if you need them. The result look different from what you have shown as your desired result, but I think this is more or less what you are actually after.