I need to generate a sequence in SQL Server 2016 database based on the following logic.

I have three fields each represents ID of Brand, Category and the Product. A brand could have multiple categories and each category could have multiple Products.

I want to generate a Sequence based on the values in these 3 fields

For example if Brand Number is 1, category is 1 and ItemID is 1 then I want 1100001. The first 1 from left represents the Brand Number, second number from left represents the category number and right most number 1 is the item number.

So for other example if the brand number is 4, category is 5 and ItemID is 100 then I need to generate 4500100.

it is working fine with the following logic (there could be a better way of writing it).

Select BrandNum*1000000 CategoryNum*100000 ItemID From Table

It works fine but this logic fails when the brand number is 10, category number is 10 (and any itemID (let's say 120)

The above code gets 1100120 but what I want is 101000120 (first 10 is brand number, second 10 is category number, and next 5 digits is for Item)

Could you please advice me what logic I should use to achieve my output?

CodePudding user response：

It seems like what you ultimately want is a NVARCHAR with segments dedicated to your categories, for the first row: 010100001 because brand is 1 (01) category is 1 (01) and product is 1 (00001).

To get this done you could just cast to NVARCHAR and then pad the resulting string:

DECLARE @Product TABLE (Brand INT, Category INT, Product INT)
INSERT INTO @Product (Brand, Category, Product) VALUES
(1 , 1 , 1  ), (1 , 2 , 1  ), (1 , 1 , 2  ), (1 , 2 , 2  ), 
(1 , 3 , 1  ), (1 , 4 , 1  ), (2 , 1 , 1  ), (2 , 1 , 2  ), 
(2 , 1 , 3  ), (2 , 1 , 4  ), (2 , 1 , 10 ), (2 , 1 , 20 ), 
(10, 10, 10 ), (11, 9 , 2  ), (2 , 10, 1  ), (2 , 1 , 200)

SELECT *, RIGHT('00' CAST(Brand AS NVARCHAR(2)),2) RIGHT('00' CAST(Category AS NVARCHAR(2)),2) RIGHT('00000' CAST(Product AS NVARCHAR(5)),5) AS SKU
  FROM @Product

Brand   Category    Product SKU
-------------------------------
1       1           1       010100001
1       2           1       010200001
1       1           2       010100002
1       2           2       010200002
1       3           1       010300001
1       4           1       010400001
2       1           1       020100001
2       1           2       020100002
2       1           3       020100003
2       1           4       020100004
2       1           10      020100010
2       1           20      020100020
10      10          10      101000010
11      9           2       110900002
2       10          1       021000001
2       1           200     020100200

CodePudding user response：

You may use CONCAT and FORMAT functions as the following:

select BrandNum, CategoryNum, ProductID,
       concat(BrandNum, CategoryNum, format(ProductID,'00000')) sequence 
from table_name

See a demo.