Reputation: 2882
Generate all combinations
I have the following table storing attributes and their values.
DECLARE @temp table
(
attribute varchar(10),
value varchar(10)
)
INSERT INTO @temp
VALUES
('height', '1.6m'),
('height', '1.8m'),
('weight', '50kg'),
('weight', '80kg'),
('gender', 'male'),
('gender', 'female')
I want to generate all the combinations of these attributes regardless of how many attributes there are in the table.
|Combination |Attribute |Value |
|------------|----------|-------|
|1 |height |1.6m |
|1 |weight |50kg |
|1 |gender |male |
|2 |height |1.6m |
|2 |weight |50kg |
|2 |gender |female |
|3 |height |1.6m |
|3 |weight |80kg |
|3 |gender |male |
|4 |height |1.6m |
|4 |weight |80kg |
|4 |gender |female |
|5 |height |1.8m |
|5 |weight |50kg |
|5 |gender |male |
|6 |height |1.8m |
|6 |weight |50kg |
|6 |gender |female |
|7 |height |1.8m |
|7 |weight |80kg |
|7 |gender |male |
|8 |height |1.8m |
|8 |weight |80kg |
|8 |gender |female |
I think CTE is needed but can't think of the way to do it. Please help.
Upvotes: 1
Views: 411
Answers (6)
Reputation: 6015
This is my attempt at a simple solution
;with
rows_cte([name], attribute, rn) as (
select v.*, row_number() over (order by (select null)) from
(select [value] h, attribute a from @temp where attribute='height') h
cross join
(select [value] w, attribute a from @temp where attribute='weight') w
cross join
(select [value] g, attribute a from @temp where attribute='gender') g
cross apply
(values (h, h.a), (w, w.a), (g, g.a)) v(n, a))
select ((rn-1)/3)+1 combination, rc.attribute, rc.[name]
from rows_cte rc
order by combination;
Upvotes: 0
Reputation: 1271131
If I understand correctly, you don't want to preprogram the number of attributes in the table. You just want all combinations however many there are.
That suggests a recursive CTE. I think it is simplest to get the combinations in strings -- all the rows for a combination on one row. And for this it helps if there is an id column in temp. So, I'll assume one exists.
So, to get the groupings of ids:
with t as (
select t.*,
dense_rank() over (order by attribute) as attribute_seqnum
from temp t
),
cte as (
select t.attribute, t.value, attribute_seqnum, 1 as lev, convert(varchar(max), t.id) as ids
from t
where attribute_seqnum = 1
union all
select t.attribute, t.value, t.attribute_seqnum, lev + 1, concat(ids, ',', t.id)
from cte join
t
on t.attribute_seqnum = cte.attribute_seqnum + 1
)
select *
from (select cte.*, max(lev) over () as max_lev
from cte
) x
where lev = max_lev;
This works by enumerating the attributes in temp and then adding them one at a time.
You can unsplit the ids and join back to the table to get separate rows:
with t as (
select t.*,
dense_rank() over (order by attribute) as attribute_seqnum
from temp t
),
cte as (
select t.attribute, t.value, attribute_seqnum, 1 as lev, convert(varchar(max), t.id) as ids
from t
where attribute_seqnum = 1
union all
select t.attribute, t.value, t.attribute_seqnum, lev + 1, concat(ids, ',', t.id)
from cte join
t
on t.attribute_seqnum = cte.attribute_seqnum + 1
)
select x.seqnum, t.attribute, t.value
from (select row_number() over (order by (select null)) as seqnum, x.*
from (select cte.*, max(lev) over () as max_lev
from cte
) x
where lev = max_lev
) x cross apply
string_split(ids, ',') s join
temp t
on t.id = convert(int, s.value);
Here is a db<>fiddle.
Note: You don't have to have an id in temp for this to work. You can store the attribute/value pairs at each step in the recursion. However, I think that just adds some additional complication.
Upvotes: 2
Reputation: 60513
with cte as
(
select
-- combination number
row_number() over (order by (select null)) as rn
,t1.attribute as a1
,t1.value as v1
,t2.attribute as a2
,t2.value as v2
,t3.attribute as a3
,t3.value as v3
-- create all combinations, each attribute needs an extra join
from @temp as t1
join @temp as t2 on t1.attribute < t2.attribute
join @temp as t3 on t2.attribute < t3.attribute
)
select c.*
from cte
-- unpivot using CROSS APPLY
cross apply
(
values
(rn, a1, v1),
(rn, a2, v2),
(rn, a3, v3)
) c (combination, attribute, value)
order by 1,2,3;
Every additional attribute requires an extra join, two columns in Select list and a VALUES in CROSS APPLY.
See fiddle
Of course this query can be created using Dynamic SQL based on select distinct attribute
Upvotes: 2
Reputation: 4042
You can combine cross join and unpivot to achieve this.
cross join
select row_number() over(order by t_h.attribute) as 'combo',
t_h.*, t_w.*, t_g.*
from @temp t_h
cross join @temp t_w
cross join @temp t_g
where t_h.attribute = 'height'
and t_w.attribute = 'weight'
and t_g.attribute = 'gender';
combo attribute value attribute value attribute value
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
1 height 1.6m weight 50kg gender male
2 height 1.6m weight 50kg gender female
3 height 1.8m weight 50kg gender male
4 height 1.8m weight 50kg gender female
5 height 1.6m weight 80kg gender male
6 height 1.6m weight 80kg gender female
7 height 1.8m weight 80kg gender male
8 height 1.8m weight 80kg gender female
unpivot
I moved the previous query into a CTE, but a subquery is also possible.
with cte_source as (
select row_number() over(order by t_h.attribute) as 'combo',
t_h.attribute as 'att_h',
t_h.value as 'val_h',
t_w.attribute as 'att_w',
t_w.value as 'val_w',
t_g.attribute as 'att_g',
t_g.value as 'val_g'
from @temp t_h
cross join @temp t_w
cross join @temp t_g
where t_h.attribute = 'height'
and t_w.attribute = 'weight'
and t_g.attribute = 'gender'
)
select up.combo,
case up.x
when 'val_h' then 'height'
when 'val_w' then 'weight'
when 'val_g' then 'gender'
end as 'attribute',
up.val as 'value'
from cte_source s
unpivot (val for x in (val_h, val_w, val_g)) up;
combo attribute value
-------------------- --------- ----------
1 height 1.6m
1 weight 50kg
1 gender male
2 height 1.6m
2 weight 50kg
2 gender female
3 height 1.8m
3 weight 50kg
3 gender male
4 height 1.8m
4 weight 50kg
4 gender female
5 height 1.6m
5 weight 80kg
5 gender male
6 height 1.6m
6 weight 80kg
6 gender female
7 height 1.8m
7 weight 80kg
7 gender male
8 height 1.8m
8 weight 80kg
8 gender female
Upvotes: 1
Reputation: 522762
The following query seems to be working, and yes, it uses a CTE:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
FROM (SELECT attribute AS attr1, value AS val1 FROM temp WHERE attribute = 'height') t1
CROSS JOIN (SELECT attribute AS attr2, value AS val2 FROM temp WHERE attribute = 'weight') t2
CROSS JOIN (SELECT attribute AS attr3, value AS val3 FROM temp WHERE attribute = 'gender') t3
)
SELECT rn AS Combination, attr1 AS Attribute, val1 AS Value FROM cte WHERE attr1 = 'height' UNION ALL
SELECT rn, attr2, val2 FROM cte WHERE attr2 = 'weight' UNION ALL
SELECT rn, attr3, val3 FROM cte WHERE attr3 = 'gender'
ORDER BY Combination, Attribute, Value;
This approach uses a series of cross joins to generate an intermediate table (the CTE) which contains one record for each combination. There are 8 combinations, given 3 attributes with 2 values each. Then, we use a series of unions to unpivot that into the long form result you want.
Upvotes: 2
Reputation: 528
maybe :
select t1.attribute, t2.value from tab t1, tab t2
or if you need unique:
select t1.attribute, t2.value
from
(select distinct attribute from tab) t1,
(select distinct value from tab ) t2
Upvotes: -1
Related Questions
- Return all possible combinations of values on columns in SQL
- Generate combinations in SQL
- T-SQL Return All Combinations of a Table
- Generate all possible combinations in SQL?
- Generate all combinations in SQL
- Query to Get all Row Combinations
- How to generate combinations
- How to generate all possible data combinations in SQL?
- What is the most effective and flexible way to generate combinations in TSQL?
- How to create a query to list all combinations possibilities