July 6, 2004 at 5:37 pm
I have a table which stores the results of a complex calculation which is applied to a large number of accounts. Of interest in this particular are two fields:
tblCalcs
acctNo int
cnt int
Which might contain the following:
acctNo cnt
----------------------------------
1 0
2 2
3 1
4 0
Now, I need to transform that data into another table which (for the purposes of the example) is as follows:
tblResult
acctNo int
And will contain the account number repeated x number of times, where x = tblCalcs.cnt.
So, in the above example, the result should be:
acctNo
-----------------
2
2
3
But I'm not quite sure how to achieve this. Ideally I'd like to do this as a single Stored Procedure. Failing that, I'll do a app-server iteration, but that strikes me as inefficient.
Ideas gratefull received.
David
July 7, 2004 at 5:24 am
It's 2 loops, nested, I think
Outer loop goes through each row in source table
Inner loop decrements the cnt value and does an insert into your target table
So that's probably a cursor for the outer loop and a while for the inner.
I cannot see any way to do this with setwise sql, but I'm not the sharpest SQL knife in the drawer, so someone may say differently. It's how I would approach the problem, and it can certainly be all done in 1 sp.
July 7, 2004 at 6:39 am
If cnt will not exceed 255 then
select c.acctNo
from tblCalcs c
inner join master.dbo.spt_values n
on n.type = 'P' and n.number between 1 and c.cnt
where c.cnt > 0
otherwise build your own number table and use that instead of master.dbo.spt_values
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply