DECLARE @A TABLE(AID INT, ASN INT)
INSERT @A SELECT 1 , 1
INSERT @A SELECT 2 , 3
INSERT @A SELECT 3, 7
DECLARE @B TABLE( BID INT, BSN VARCHAR(10))
INSERT @B SELECT 1 , 'A'
INSERT @B SELECT 2, 'B'
SELECT LTRIM(ASN)+BSN FROM @A,@B ORDER BY ASN
/*----------------------
1A
1B
3A
3B
7A
7B
*/
INSERT @A SELECT 1 , 1
INSERT @A SELECT 2 , 3
INSERT @A SELECT 3, 7
DECLARE @B TABLE( BID INT, BSN VARCHAR(10))
INSERT @B SELECT 1 , 'A'
INSERT @B SELECT 2, 'B'
SELECT LTRIM(ASN)+BSN FROM @A,@B ORDER BY ASN
/*----------------------
1A
1B
3A
3B
7A
7B
*/
DECLARE @A TABLE(AID INT, ASN INT)
INSERT @A SELECT 1 , 1
INSERT @A SELECT 2 , 3
INSERT @A SELECT 3, 7
DECLARE @B TABLE( BID INT, BSN VARCHAR(10))
INSERT @B SELECT 1 , 'A'
INSERT @B SELECT 2, 'B'
SELECT CID=ROW_NUMBER() OVER(ORDER BY GETDATE()),
CSN=LTRIM(ASN)+BSN
FROM @A,@B ORDER BY ASN
/*
CID CSN
-------------------- ----------------------
1 1A
4 1B
5 3B
2 3A
3 7A
6 7B
*/
SELECT CID=ROW_NUMBER() OVER(ORDER BY ASN),
CSN=LTRIM(ASN)+BSN
FROM @A,@B ORDER BY ASN