Geek asks and answers

View to return a string duplicated # times represented by the value of the sum field

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

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

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

The request / presentation should return 480 rows (one for each penny), which look like this:

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

What will be the most effective way to achieve this? I have a solution that uses a table-based function and a temporary table, but in the back of my head I always think that this needs to be done using the traditional view. Perhaps a creative cross join or something that returns this result without declaring too many resources in the form of temporary tables, but tbf, etc. Any ideas?

+3
source share
2 answers

With a small numbers table, you can do the following:

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 and want to check it out, 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:
.
"Numbers" "Tally" : .

Tally - , , 0 1 ( 1) . Tally . , , . VARCHAR (8000) , 8000 . 30 , Tally 11 000 , 365,25 30 .

+3

CTE, - :

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);

SqlFiddle

, 0. , ( ). 32676 - , ( - 100). , 32676 , , , :)

+3

More articles:


All Articles