SQL Server dynamic CTE rows-CodePudding

I want to get result like below

seqno seqno_ups_desc  
--------------------
108 108A  
108 108B  
108 108C  
108 108D  
109 109A  
109 109B  
109 109C  
109 109D  
110 110A  
110 110B  
110 110C  
110 110D  
111 111A  
111 111B  
111 111C  
111 111D

I currently have result like this, but I am unable to increment it to 109 and above

seqno seqno_ups_desc  
--------------------
108 A  
108 B  
108 C  
108 D

This is my code

DECLARE @OrderID INT = 215332, @MCHID INT = 188, @bbs_ups INT

SET @bbs_ups = 4

;WITH CreateBundleSticker([counter], bundle_seqno, bundle_seqno_ups) AS 
(
    SELECT 
        1 counter, 
        ISNULL((SELECT TOP 1 bbd_bundle_seqno 
                FROM BAG_BundleStickerDetails 
                WHERE bbd_order_id_fk = @OrderID 
                  AND bbd_mch_id_fk = @MCHID 
                  AND bbd_status = 'A' 
                ORDER BY bbd_id DESC), 0), CHAR(ASCII('A')) [char]
    UNION ALL

    SELECT
        [counter]   1, bundle_seqno, 
        CHAR(ASCII(bundle_seqno_ups)   1)
    FROM
        CreateBundleSticker
    WHERE
        [counter] < @bbs_ups
)
SELECT * 
FROM CreateBundleSticker

CodePudding user response：

WITH
  numbers(id) AS
(
              SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
),
  letters(id, val) AS
(
  SELECT
    id,
    CHAR(ASCII('A')   id)
  FROM
    numbers
),
  sequence_base AS
(
  SELECT TOP 1
    bbd_bundle_seqno
  FROM
    BAG_BundleStickerDetails
  WHERE
        bbd_order_id_fk = @OrderID
    AND bbd_mch_id_fk = @MCHID
    AND bbd_status = 'A'
  ORDER BY
    bbd_id DESC
)
SELECT
  ISNULL(sequence_base.bbd_bundle_seqno, 1)   numbers.id,
  letters.val
FROM
  numbers
CROSS JOIN
  letters
LEFT JOIN
  sequence_base
    ON 1=1

CodePudding user response：

DECLARE @OrderID INT = 215332, @MCHID INT = 188, @bbs_ups INT

SET @bbs_ups = 4

;WITH CreateBundleSticker([counter], bundle_seqno, bundle_seqno_ups) AS
(
    SELECT 
        1 counter,
        ISNULL((SELECT TOP 1 bbd_bundle_seqno
                FROM BAG_BundleStickerDetails
                WHERE bbd_order_id_fk = @OrderID
                  AND bbd_mch_id_fk = @MCHID
                  AND bbd_status = 'A'
                ORDER BY bbd_id DESC), 0), CHAR(ASCII('A')) [char]
    UNION ALL

    SELECT
        [counter]   1, bundle_seqno, 
        CHAR(ASCII(bundle_seqno_ups)   1)
    FROM
        CreateBundleSticker
    WHERE
        [counter] < @bbs_ups

)
SELECT bbd_bundle_seqno ' ' bbd_bundle_seqno ' ' bundle_seqno_ups AS Result
FROM BAG_BundleStickerDetails bsd,CreateBundleSticker cbs
GROUP BY bbd_bundle_seqno,bundle_seqno_ups