SQL Server 2022

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

CREATE TABLE #NUMBERS
(
    N BIGINT
);
INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;
9 rows affected

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT   X.*
    FROM     A
    CROSS APPLY
    (
        VALUES
        (A.N, 'METERS'),
        (A.N*100, 'CENTIMETERS'),
        (A.N*1000, 'MILLIMETERS'),
        (A.N*1000000, 'MICRONS'),
        (A.N*1000000000, 'NANOMETERS')
    ) AS X (QUANTITY, UNIT)
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;


QUANTITY
UNIT


3
METERS

300
CENTIMETERS

3000
MILLIMETERS

3000000
MICRONS

3000000000
NANOMETERS

QUANTITY	UNIT
3	METERS
300	CENTIMETERS
3000	MILLIMETERS
3000000	MICRONS
3000000000	NANOMETERS