Try this, a variant of sunitabeck's query. It seems to do what you're after.;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE N < 50),N2(item, RN) AS ( SELECT a.item, ROW_NUMBER() OVER(PARTITION BY a.item ORDER BY (SELECT NULL)) AS RN FROM tbl a INNER JOIN N ON n.n <= a.qty)SELECT LEFT(item, LEN(item) - 3) + CASE WHEN RN > 100 THEN '' WHEN RN > 100 THEN '0' ELSE '00' END + CAST(RN AS VARCHAR(10)), 1 AS Qty FROM N2
This assumes that the last 3 characters if each item are numerals.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.