Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Incremental count based on quantiy

Author  Topic 

rwaldron
Posting Yak Master

131 Posts

Posted - 2012-03-08 : 07:34:58
Could someone please help me with this sql query.I need incremental count based on quantity. And can this be implemented using an excel formula?
Thx
Ray..

I Have the following
item qty
test1item001 4
test2item001 2
test3item001 1

But I want below.. Im not to pushed about the quantity field but I need to print the items.
item qty
test1item001 1
test1item002 1
test1item003 1
test1item004 1
test2item001 1
test2item002 1
test3item001 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-08 : 08:08:46
You can certainly do this via T-SQL, but I am concerned about the business need for such a thing. When a nice set of data needs to be expanded like this, more likely than not, it may be the result of something not quite right in the businss requirements, or specifications. Nonetheless, rather than preaching from my soapbox, here is away to do what you are asking for:
;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE N < 50)
SELECT
a.item,
ROW_NUMBER() OVER(PARTITION BY a.item ORDER BY (SELECT NULL)) AS RN,
1 AS Qty
FROM
YourTable a
INNER JOIN N ON n.n <= a.qty;
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2012-03-19 : 17:32:42
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.
Go to Top of Page
   

- Advertisement -