SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE unit_dimension(
    id   INT IDENTITY(1,1) NOT NULL,
    name NVARCHAR(32)      NOT NULL,
);
INSERT INTO unit_dimension(name) VALUES ('distance'), ('time');

CREATE TABLE unit(
    id             INT IDENTITY(1,1) NOT NULL,
    name           NVARCHAR(32)      NOT NULL,
    plural         NVARCHAR(32)      NOT NULL,
    symbol         NVARCHAR(32)      NOT NULL,
    dimension_id   INT               NOT NULL,
    size           FLOAT             NOT NULL
);

INSERT INTO
  unit(name, plural, symbol, dimension_id, size)
VALUES
  (N'Inch',       N'Inches',      N'in', 1,                1.00),
  (N'Foot',       N'Feet',        N'ft', 1,               12.00),
  (N'Yard',       N'Yards',       N'yd', 1,               36.00),
  (N'Mile',       N'Miles',       N'mi', 1,            63360.00),
  (N'Meter',      N'Meters',       N'm', 1,   39.37007874015748),
  (N'Light Year', N'Light Years', N'ly', 1, 9460730472580800.00),
  (N'Angrstom',   N'Angstroms',    N'Å', 1,      3.937007874e-9)
;
9 rows affected

CREATE VIEW v_UOMC AS
  SELECT
    x.id              AS from_id,
    x.name            AS from_name,
    y.id              AS to_id,
    y.name            AS to_name,
    x.size / y.size   AS conversion_factor
  FROM
    unit   x
  INNER JOIN
    unit   y
      ON  x.dimension_id = y.dimension_id
;
SELECT * FROM v_UOMC;


from_id
from_name
to_id
to_name
conversion_factor


1
Inch
1
Inch
1

2
Foot
1
Inch
12

3
Yard
1
Inch
36

4
Mile
1
Inch
63360

5
Meter
1
Inch
39.3700787401575

6
Light Year
1
Inch
9.4607304725808E+15

7
Angrstom
1
Inch
3.937007874E-09

1
Inch
2
Foot
0.0833333333333333

2
Foot
2
Foot
1

3
Yard
2
Foot
3

4
Mile
2
Foot
5280

5
Meter
2
Foot
3.28083989501312

6
Light Year
2
Foot
788394206048400

7
Angrstom
2
Foot
3.280839895E-10

1
Inch
3
Yard
0.0277777777777778

2
Foot
3
Yard
0.333333333333333

3
Yard
3
Yard
1

4
Mile
3
Yard
1760

5
Meter
3
Yard
1.09361329833771

6
Light Year
3
Yard
262798068682800

7
Angrstom
3
Yard
1.09361329833333E-10

1
Inch
4
Mile
1.57828282828283E-05

2
Foot
4
Mile
0.000189393939393939

3
Yard
4
Mile
0.000568181818181818

4
Mile
4
Mile
1

5
Meter
4
Mile
0.000621371192237334

6
Light Year
4
Mile
149317084478.864

7
Angrstom
4
Mile
6.21371192234849E-14

1
Inch
5
Meter
0.0254

2
Foot
5
Meter
0.3048

3
Yard
5
Meter
0.9144

4
Mile
5
Meter
1609.344

5
Meter
5
Meter
1

6
Light Year
5
Meter
240302554003552

7
Angrstom
5
Meter
9.99999999996E-11

1
Inch
6
Light Year
1.05700083402462E-16

2
Foot
6
Light Year
1.26840100082954E-15

3
Yard
6
Light Year
3.80520300248862E-15

4
Mile
6
Light Year
6.69715728437996E-12

5
Meter
6
Light Year
4.16142060639612E-15

6
Light Year
6
Light Year
1

7
Angrstom
6
Light Year
4.16142060637948E-25

1
Inch
7
Angrstom
254000000.001016

2
Foot
7
Angrstom
3048000000.01219

3
Yard
7
Angrstom
9144000000.03658

4
Mile
7
Angrstom
16093440000064.4

5
Meter
7
Angrstom
10000000000.04

6
Light Year
7
Angrstom
2.40302554004514E+24

7
Angrstom
7
Angrstom
1


-- If I were to walk 5000 miles, how far would that be in meters?
SELECT
  5000 * conversion_factor
FROM
  v_UOMC
WHERE
      from_name = 'Mile'
  AND   to_name = 'Meter'
;


(No column name)


8046720

from_id	from_name	to_id	to_name	conversion_factor
1	Inch	1	Inch	1
2	Foot	1	Inch	12
3	Yard	1	Inch	36
4	Mile	1	Inch	63360
5	Meter	1	Inch	39.3700787401575
6	Light Year	1	Inch	9.4607304725808E+15
7	Angrstom	1	Inch	3.937007874E-09
1	Inch	2	Foot	0.0833333333333333
2	Foot	2	Foot	1
3	Yard	2	Foot	3
4	Mile	2	Foot	5280
5	Meter	2	Foot	3.28083989501312
6	Light Year	2	Foot	788394206048400
7	Angrstom	2	Foot	3.280839895E-10
1	Inch	3	Yard	0.0277777777777778
2	Foot	3	Yard	0.333333333333333
3	Yard	3	Yard	1
4	Mile	3	Yard	1760
5	Meter	3	Yard	1.09361329833771
6	Light Year	3	Yard	262798068682800
7	Angrstom	3	Yard	1.09361329833333E-10
1	Inch	4	Mile	1.57828282828283E-05
2	Foot	4	Mile	0.000189393939393939
3	Yard	4	Mile	0.000568181818181818
4	Mile	4	Mile	1
5	Meter	4	Mile	0.000621371192237334
6	Light Year	4	Mile	149317084478.864
7	Angrstom	4	Mile	6.21371192234849E-14
1	Inch	5	Meter	0.0254
2	Foot	5	Meter	0.3048
3	Yard	5	Meter	0.9144
4	Mile	5	Meter	1609.344
5	Meter	5	Meter	1
6	Light Year	5	Meter	240302554003552
7	Angrstom	5	Meter	9.99999999996E-11
1	Inch	6	Light Year	1.05700083402462E-16
2	Foot	6	Light Year	1.26840100082954E-15
3	Yard	6	Light Year	3.80520300248862E-15
4	Mile	6	Light Year	6.69715728437996E-12
5	Meter	6	Light Year	4.16142060639612E-15
6	Light Year	6	Light Year	1
7	Angrstom	6	Light Year	4.16142060637948E-25
1	Inch	7	Angrstom	254000000.001016
2	Foot	7	Angrstom	3048000000.01219
3	Yard	7	Angrstom	9144000000.03658
4	Mile	7	Angrstom	16093440000064.4
5	Meter	7	Angrstom	10000000000.04
6	Light Year	7	Angrstom	2.40302554004514E+24
7	Angrstom	7	Angrstom	1