3

I have an accounting table that contains a dollar amount field. I want to create a view that will return one row per penny of that amount with some of the other fields from the table.

So as a very simple example let's say I have a row like this:

PK     Amount     Date
---------------------------
123    4.80       1/1/2012

The query/view should return 480 rows (one for each penny) that all look like this:

PK      Date
-----------------
123     1/1/2012

What would be the most performant way to accomplish this? I have a solution that uses a table valued function and a temp table but in the back of my head I keep thinking there has got to be a way to accomplish this with a traditional view. Possibly a creative cross join or something that will return this result without having to declare too many resources in the form of temp tables, and tbf's etc. Any ideas?

RThomas
  • 10,702
  • 2
  • 48
  • 61

2 Answers2

3

You could use a CTE, something like this:

WITH duplicationCTE AS 
(
    SELECT PK, Date, Amount, 1 AS Count
    FROM myTable
    UNION ALL
    SELECT myTable.PK, myTable.Date, myTable.Amount, Count+1
    FROM myTable
         JOIN duplicationCTE ON myTable.PK = duplicationCTE.PK
    WHERE Count+1 <= myTable.Amount*100
)
SELECT PK, Date
FROM duplicationCTE
OPTION (MAXRECURSION 0);

Here is the SqlFiddle

AND, do note the 0. That means that this can run infinitely (dangerous btw) Otherwise, 32676 is the max number of recursions you can set (default is 100). However, if you are running over 32676 loops, then maybe you need to rethink your logic :)

Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
  • You can also set MAXRECURSION to 0 for no limit. – Tim Lehner Apr 13 '12 at 18:22
  • This works, but the size of my tables and the recursion involved to make it all happen is prohibitive. – RThomas Apr 13 '12 at 18:28
  • Gosh... be careful folks. Please see the following article for why you shouldn't use recursive CTE's to count. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Jeff Moden Oct 10 '12 at 03:52
3

With a little help of a numbers table you can do like this:

select PK, [Date]
from YourTable as T
  inner join number as N
    on N.n between 1 and T.Amount * 100

If you don't have one around and you want to test this you can use master..spt_values.

declare @T table
(
  PK int,
  Amount money,
  [Date] date
)

insert into @T values
(123,    4.80,       '20120101')


;with number(n) as
(
  select number 
  from master..spt_values
  where type = 'P'
)
select PK, [Date]
from @T as T
  inner join number as N
    on N.n between 1 and T.Amount * 100

Update:
From an article by Jeff Moden.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

A Tally table is nothing more than a table with a single column of very well indexed sequential numbers starting at 0 or 1 (mine start at 1) and going up to some number. The largest number in the Tally table should not be just some arbitrary choice. It should be based on what you think you'll use it for. I split VARCHAR(8000)'s with mine, so it has to be at least 8000 numbers. Since I occasionally need to generate 30 years of dates, I keep most of my production Tally tables at 11,000 or more which is more than 365.25 days times 30 years.

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Where is the number table coming from? Is the OP supposed to create a table with all possible numbers? Or maybe I am missing something – Justin Pihony Apr 13 '12 at 18:22
  • 2
    Yes, a numbers or [Tally table](http://www.sqlservercentral.com/articles/T-SQL/62867/) comes in handy for many things. – Tim Lehner Apr 13 '12 at 18:26
  • 1
    This is exactly what I was hoping for... I could picture the cross join... my imagination just hadn't gotten as far as having a "numbers table". The link is solid gold also. +1, I'll be back to accept after I build and test. – RThomas Apr 13 '12 at 18:27
  • Nicely done, Mikael. And thank your for the mention. I truly appreciate it. – Jeff Moden Oct 10 '12 at 03:51