Serial number issue in SQL Server 2008

Question

Hello experts, I have orders and order details table as shown as below:

OrderDetails

Order_ID   int 
Order_Code varchar(10)
Product Name   varchar(50)
Qty     Int
SeqNO   varchar(10)

Below are the sample records

10001    OC   Ottoman   10  Null
10002    OC   Ottoman   3   Null  
10003    OC   Ottoman   2   Null
10004    OC   Ottoman   2   Null

Is there anyway I can update the SeqNo column with 1, 1A,1B,1C if the Order_Code is same else it should increment by one.

for example

10001    OC   Ottoman   10  Null
10002    OD   Ottoman   3   Null  
10003    OE   Ottoman   2   Null
10004    OF   Ottoman   2   Null

this one should be 1,2,3,4

Please help

Answer 1

Not sure if I understand everything, but take a look at this example:

DECLARE @OrderDetails TABLE
  ( 
   Order_ID int
  ,Order_Code varchar(10)
  ,Product_Name varchar(15)
  ,Qty int
  )

INSERT  INTO @OrderDetails
        ( Order_ID, Order_Code, Product_Name, Qty)
VALUES  ( 10001, 'OC', 'Ottoman', 10 )
,       ( 10002, 'OC', 'Ottoman', 3 )
,       ( 10003, 'OC', 'Ottoman', 2 )
,       ( 10004, 'OC', 'Ottoman', 2 )
,       ( 10005, 'SF', 'Sofa', 2 )
,       ( 10006, 'SF', 'Sofa', 7 )

;
WITH  abcd
        AS ( SELECT Order_ID
                   ,Order_Code
                   ,Product_Name
                   ,Qty
                   ,DENSE_RANK() OVER ( ORDER BY Order_Code ) AS [RnkSeq]
                   ,ROW_NUMBER() OVER ( PARTITION BY Order_Code ORDER BY Order_ID ) AS [NumSeq]
             FROM   @OrderDetails
           )
  SELECT  Order_ID
         ,Order_Code
         ,Product_Name
         ,Qty
         ,CAST(RnkSeq AS varchar(10)) + CHAR(64 + NumSeq) AS SeqNo
  FROM    abcd

Returns

Order_ID    Order_Code Product_Name    Qty         SeqNo
----------- ---------- --------------- ----------- -----------
10001       OC         Ottoman         10          1A
10002       OC         Ottoman         3           1B
10003       OC         Ottoman         2           1C
10004       OC         Ottoman         2           1D
10005       SF         Sofa            2           2A
10006       SF         Sofa            7           2B

Hope this helps.

UPDATE

To update and have 1, 1A, 1B.. use:

;
WITH  abcd
        AS ( SELECT Order_ID
                   ,Order_Code
                   ,Product_Name
                   ,Qty
                   ,DENSE_RANK() OVER ( ORDER BY Order_Code ) AS [RnkSeq]
                   ,ROW_NUMBER() OVER ( PARTITION BY Order_Code ORDER BY Order_ID ) AS [NumSeq]
             FROM   @OrderDetails
           ),
      efgh
        AS ( SELECT Order_ID
                   ,Order_Code
                   ,Product_Name
                   ,Qty
                   ,CAST(RnkSeq AS varchar(10)) + CHAR(63 + NumSeq) AS [Seq]
             FROM   abcd
           )
  UPDATE  @OrderDetails
  SET     SeqNo = REPLACE(e.Seq,'@','')
  FROM    @OrderDetails AS o
          JOIN efgh AS e ON e.Order_ID = o.Order_ID

Beware what happens when you get more than 1, 1A, ... 1Z different order numbers for Ottomans.