StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Not sure if I understand everything, but take a look at this example:</p> <pre><code>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 </code></pre> <p>Returns</p> <pre><code>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 </code></pre> <p>Hope this helps.</p> <p><strong>UPDATE</strong></p> <p>To update and have <code>1, 1A, 1B..</code> use:</p> <pre><code>; 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 </code></pre> <p>Beware what happens when you get more than <code>1, 1A, ... 1Z</code> different order numbers for Ottomans.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload