CREATE TABLE #SeqTbl ( Seq int NOT NULL DEFAULT ( 0 ) , C1 varchar( 10 ) NULL , C2 varchar( 20 ) NULL , UNIQUE ( C1 , C2 ) );
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
GO
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
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;
GO DROP TABLE #SeqTbl; |