Generate Number Bale_ID base on Bale_Type

Question

I want to generate Bale_ID base on Bale type ,

Create table #tbl_Bale_Prd (Bale_ID varchar,Bale_Type int)

  ;WITH cte AS (SELECT 1 AS Bale_ID
                            UNION ALL
                            SELECT TOP (1) stuff(Bale_ID, 1, 3, '') + 1 AS Bale_ID
                            FROM     #tbl_Bale_Prd AS t
                            ORDER BY Bale_ID DESC)
      SELECT TOP (1)  CONCAT('BB-', Bale_ID)  AS Bale_ID
      FROM     cte
      ORDER BY Bale_ID DESC

Means that
Bale_type =1 then generate BB-10001
Bale_type =2 then generate BL-10001

Answer 1

Maybe like this?

    SELECT *,CASE WHEN Bale_type =1 THEN 'BB-10001'  
    WHEN Bale_type =2 THEN 'BL-10001' END Bale_ID  
    FROM #tbl_Bale_Prd  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".