Binaryworld - How to generate sequence numbers based on values in multiple columns? ... [ SQL Server -> T-SQL ]

This is similar to doing an ORDER BY on the required columns & counting the rows from the top.

CREATE TABLE #SeqTbl (
    Seq int NOT NULL DEFAULT ( 0 ) ,
    C1 varchar( 10 ) NULL ,
    C2 varchar( 20 ) NULL ,
    UNIQUE ( C1 , C2 )
);
-- Sample data
INSERT INTO #SeqTbl ( C1 , C2 )
SELECT 'a' AS c1 , 'b' AS c2
UNION ALL
SELECT 'x' , 'y'
UNION ALL
SELECT '1' , NULL
UNION ALL
SELECT '*' AS c1 , '$' AS c2
UNION ALL
SELECT NULL , 'y'
UNION ALL
SELECT NULL , NULL;

SELECT * FROM #SeqTbl
/*
Seq     C1     C2
----------- ---------- --------------------
     0 a     b
     0 x     y
     0 1     NULL
     0 *     $
     0 NULL    y
     0 NULL    NULL
*/
GO
/*
    Objective: To sequence the rows in the order of C1 , C2. The
          sequence numbers should match say a query with ORDER BY
          clause of C1 , C2 viz: "SELECT * FROM tbl ORDER BY C1, C2".
    Solution : Concatenate the two columns & count the values
          less than or equal to a particular value.
          The values should always be converted to fixed-length for
          this logic to work. The CHAR(0) value for NULLs is used to
          sort the NULL values on top. You can use a similar technique
          to sort the NULL values at the bottom.
*/
-- Query that shows the concatenation results that will be used:
SELECT COALESCE( CONVERT( char( 10 ) , t1.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
    COALESCE( CONVERT( char( 20 ) , t1.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) ) AS OrderCols
FROM #SeqTbl AS t1
/*
OrderCols
------------------------------

*     $
1
a     b
x     y
*/
UPDATE #SeqTbl
SET Seq = ( SELECT COUNT( * ) FROM #SeqTbl AS t2
       WHERE COALESCE( CONVERT( char( 10 ) , t2.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
          COALESCE( CONVERT( char( 20 ) , t2.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) )
          <=
          COALESCE( CONVERT( char( 10 ) , #SeqTbl.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
          COALESCE( CONVERT( char( 20 ) , #SeqTbl.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) )
);
SELECT * FROM #SeqTbl
ORDER BY Seq;
/*
Seq     C1     C2
----------- ---------- --------------------
     1 NULL    NULL
     2 NULL    y
     3 *     $
     4 1     NULL
     5 a     b
     6 x     y
*/
GO
DROP TABLE #SeqTbl;