CODEWITHSUNDEEP
sqlsql-servercombinationscombinatorics
Paolo
Paolo

Reputation: 183

Enumerate (give a name) the records of a cross join of two or more tables

I need to give a unique identifier to any single row resulting from a cross join (cross products) of two tables (two sets) with SQL Server. Basically i need to "condense" the columns values of any row of the result set in a unique identifier. I was thinking a GUID approach or something similar but I am not sure it serves the purpose. What approach can be valid with SQL Sserver? The purpose of this mechanism is to evaluate all the possible combinations of members (rows) of different sets (tables) and assign a unique name to any combination needed for later use.

Example

Input Tables A and B

+----+----+   +----+----+
| Table A |   | Table B |
+----+----+   +----+----+
| A1 | A2 |   | B1 | B2 |
+----+----+   +----+----+
|  1 |  2 |   | a  |  b |
|  3 |  4 |   | c  |  d |
+----+----+   +----+----+

Result Set Table A X B + Unique Identifier

+----+----+----+----+ +-----+
| A1 | A2 | B1 | B2 | | UID |
+----+----+----+----+ +-----+
|  1 |  2 | a  |  b | | 001 |
|  1 |  2 | c  |  d | | 002 |
|  3 |  4 | a  |  b | | 003 |
|  3 |  4 | c  |  d | | 004 |
+----+----+----+----+ +-----+

Upvotes: 0

Views: 111

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want a unique identifier that is consistent over time, then combine the two identifiers in the tables:

concat(a1, ':', b1)

This assumes that a1 and b1 are primary keys.

If you don't have primary keys, then add one. That will facilitate what you want to do.

You can take the risk of using hashing functions. Something like this:

select concat(convert(varchar(32), hashbytes('MD5', concat(a1, ':', a2), 2), ':',
              convert(varchar(32), hashbytes('MD5', concat(b1, ':', b2), 2)
             )

This runs the risk of collisions (hash values that are the same even though the underlying values are not the same), but that is pretty low. It is an argument for using primary keys.

Upvotes: 1

GMB
GMB

Reputation: 222622

An option is to use row_number():

select
    a.a1,
    a.a2,
    b.b1,
    b.b2,
    row_number() over(order by a.a1, a.a2, b.b1, b.b2) uid
from a
cross join b

Upvotes: 1

Related Questions