Unique combination of multiple columns, order doesn't matter

Question

Suppose a table with 3 columns. each row represents a unique combination of each value:

a a a
a a b
a b a
b b a
b b c
c c a
...

however, what I want is,

aab = baa = aba 
cca = cac = acc
...

Finally, I want to get these values in a CSV format as a combination for each value like the image that I attached.

this

Thanks for your help!

Below is the query to generate my problem, please take a look!

--=======================================
--populate test data
--=======================================
drop table if exists #t0
;
with 
cte_tally as
(
select row_number() over (order by (select 1)) as n 
from sys.all_columns
)
select 
  char(n) as alpha
into #t0
from 
  cte_tally
where
  (n > 64 and n < 91) or
  (n > 96 and n < 123);

drop table if exists #t1
select distinct upper(alpha) alpha into #t1 from #t0

drop table if exists #t2
select
    a.alpha c1
,   b.alpha c2
,   c.alpha c3
,   row_number()over(order by (select 1)) row_num
into #t2
from #t1 a
join #t1 b on 1=1
join #t1 c on 1=1


drop table if exists #t3
select *
into #t3
from (
    select *
    from #t2
) p
unpivot
    (cvalue for c in (c1,c2,c3)
) unpvt


select
    row_num
,   c
,   cvalue
from #t3
order by 1,2

--=======================================
--these three rows should be treated equally
--=======================================
select *
from #t2
where concat(c1,c2,c3) in  ('ABA','AAB', 'BAA')

--=======================================
--what i've tried...
--row count is actually correct, but the problem is that it ommits where there're any duplicate alphabet.
--=======================================
select 
    distinct
    stuff((
        select
            distinct
        '.' + cvalue
        from #t3 a
        where a.row_num = h.row_num
    for xml path('')
    ),1,1,'') as comb
from #t3 h

Unpivot, sort, pivot, concatenate. Show us your unsuccessful query attempts! — Arvo, Jul 15 '21 at 06:08
In my opinion, it would be much easier to pull the rows as is and do the combinations in a programming language. — Tim Roberts, Jul 15 '21 at 06:21
I coudn't find any post about my case, it seems like everything is about cross join to find distinct values from multiple columns which is not exactly what i'm looking for. — Hoon Choi, Jul 15 '21 at 08:21
Do you care about the element order in your output csv? For instance, you might get 37,30,30 or you might get 30,37,30, etc., but for your output do you need a standard ordering of the elements, like 37,30,30, or is any order acceptable? — Chris Strickland, Jul 15 '21 at 15:15
30,30,37 would be the best result for 30,30,37/30,37,30/37,30,30 — Hoon Choi, Jul 15 '21 at 22:49

score 1 · Accepted Answer · answered Jul 15 '21 at 11:03

1

As pointed out in the comments, you can unpivot the values, sort them in the right order and reaggregate them into a single row. Then you can group the original rows by those new values.

SELECT *
FROM #t2
CROSS APPLY (
    SELECT a = MIN(val), b = MIN(CASE WHEN rn = 2 THEN val), c = MAX(val)
    FROM (
        SELECT *, rn = ROW_NUMBER() OVER (ORDER BY val)
        FROM (VALUES (c1),(c2),(c3) ) v3(val)
    ) v2
) v
GROUP BY v.a, v.b, v.c;

Really, what you should perhaps do, is ensure that the values are in the correct order in the first place:

ALTER TABLE #t2
   ADD CONSTRAINT t2_ValuesOrder
       CHECK (c1 <= c2 AND c2 <= c3);

answered Jul 15 '21 at 11:03

Charlieface

52,284
6
19
43

This solved my problem .thank you! Unpivot using cross apply is something I didn't know about. – Hoon Choi Jul 15 '21 at 23:39
@HoonChoi `CROSS APPLY` is the best thing since sliced bread. See also https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one – Charlieface Jul 15 '21 at 23:54

score 0 · Answer 2 · answered Jul 15 '21 at 07:41

Would be curious why, sure you have a reason. Might suggest having a lookup table, holding all associated keys to a "Mapping Table". You might optimize some of this as you implement it. First create one table for holding the "Next/New Key" (this is where the 1, 2, 3...) come from. You get a new "New Key" after each batch of records you bulk insert into your "Mapping Table". The "Mapping Table" holds the combination of the key values, one row for each combinations along with your "New Key" Should get a table looking something like:

A, B, C, 1
A, C, B, 1
B, A, C, 1
...
X, Y, Z, 2
X, Z, Y, 2

If you can update your source table to hold a column for your "Mapping Key" (the 1,2,3) then you just look up from the mapping table where (c1=a, c2=a, c3=b) order for this look-up shouldn't matter. One suggestion would create a composite unique key using c1,c2,c3 on your mapping table. Then to get your records just look up the "mapping key value" from the mapping table and then query for records matching the mapping key value. Or, if you don't do a pre-lookup to get the mapping key you should be able to do a self-join using the mapping key value...

your suggestion is to create a mapping table for each combination to a certain(one) value. however the problem would be how to create the mapping table. — Hoon Choi, Jul 15 '21 at 08:18
Well what I suggested would work and most times i've seen where lexicaly "ABC" == "CAB" or any combination, you will soon need "ABC" == "XYZ", the solution suggested would handle that as well? Anyway thought i mentioned the process to create the mapping table? — Dano, Jul 15 '21 at 15:53

score 0 · Answer 3 · answered Jul 15 '21 at 11:51

0

If you want them in a CSV format:

select distinct v.cs
from #t2 t2 cross apply
     (select string_agg(c order by c desc, ',') as cs
      from (values (t2.c1), (t2.c2), (t2.c3)
           ) v(c)
     ) v;

answered Jul 15 '21 at 11:51

Gordon Linoff

1,242,037
58
646
786

Chris Strickland · Answer 4 · 2021-07-15T20:47:52.733

It seems to me that what you need is some form of masking*. Take this fiddle:

http://sqlfiddle.com/#!18/fc67f/8

where I have created a mapping table that contains all of the possible values and paired that with increasing orders of 10. Doing a cross join on that map table, concatenating the values, adding the masks and grouping on the total will yield you all the unique combinations.

Here is the code from the fiddle:

CREATE TABLE maps (
  val varchar(1),
  num int
);

INSERT INTO maps (val, num) VALUES ('a', 1), ('b', 10), ('c', 100);

SELECT mask, max(vals) as val 
FROM (
  SELECT concat(m1.val, m2.val, m3.val) as vals,
      m1.num + m2.num + m3.num as mask
  FROM maps m1
  CROSS JOIN maps m2
  CROSS JOIN maps m3
) q GROUP BY mask

Using these values of 10 will ensure that mask contains the count for each value, one for each place column in the resulting number, and then you can group on it to get the unique(ish) strings.

I don't know what your data looks like, and if you have more than 10 possible values then you will have to use some other base than 10, but the theory should still apply. I didn't write code to extract the columns from the value table into the mapping table, but I'm sure you can do that.

*actually, I think the term I was looking for was flag.

Unique combination of multiple columns, order doesn't matter

4 Answers4