How can I pivot data on a range of integers?

17 Aug 2006 5:39 PM

McDale

Hey everyone,

I have a situation where I need to take one record and split it up over a
range of integers that could be different for each record. The result would
be one record for each integer in that range. Here is an example:

The table contains
START_NBR int
END_NBR int
SIZE_OF_RANGE int
SALES_AMT money

What I need to do is take each record from this, and create one record per
number in the range, fitting the structure of:
CURR_NBR int
SALE_AMT money

So for example, if the record were

START_NBR = 1
END_NBR = 3
SIZE_OF_RANGE = 3
SALES_AMT = 12.00

I would want 3 records returned like:
1, 4.00
2, 4.00
3, 4.00

I know I could do this if I joined to a table containing a range of
integers, but I would rather do this "on the fly" than create a dummy table.
I also tried creating a FUNCTION that returns an integer range that I could
select from in the join. However, it will not allow me to use the START_NBR
and END_NBR from the first table as the input values for the FUNCTION.

If anyone knows of a clean way to do this, please let me know.

Thanks!
Mike

17 Aug 2006 7:18 PM

Arnie Rowland

This is one of the 'valid' reasons to have a Numbers table, or at least a
table defined FUNCTION that creates one for you.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"McDale" <McD***@discussions.microsoft.com> wrote in message
news:44688C59-0F0E-446E-A887-894B0D6DC4D2@microsoft.com...
> Hey everyone,
>
> I have a situation where I need to take one record and split it up over a
> range of integers that could be different for each record. The result
> would
> be one record for each integer in that range. Here is an example:
>
> The table contains
> START_NBR int
> END_NBR int
> SIZE_OF_RANGE int
> SALES_AMT money
>
> What I need to do is take each record from this, and create one record per
> number in the range, fitting the structure of:
> CURR_NBR int
> SALE_AMT money
>
> So for example, if the record were
>
> START_NBR = 1
> END_NBR = 3
> SIZE_OF_RANGE = 3
> SALES_AMT = 12.00
>
> I would want 3 records returned like:
> 1, 4.00
> 2, 4.00
> 3, 4.00
>
> I know I could do this if I joined to a table containing a range of
> integers, but I would rather do this "on the fly" than create a dummy
> table.
> I also tried creating a FUNCTION that returns an integer range that I
> could
> select from in the join. However, it will not allow me to use the
> START_NBR
> and END_NBR from the first table as the input values for the FUNCTION.
>
> If anyone knows of a clean way to do this, please let me know.
>
> Thanks!
> Mike
>

17 Aug 2006 10:01 PM

rpresser

McDale wrote:
Show quote

> Hey everyone,
>
> I have a situation where I need to take one record and split it up over a
> range of integers that could be different for each record. The result would
> be one record for each integer in that range. Here is an example:
>
> The table contains
> START_NBR int
> END_NBR int
> SIZE_OF_RANGE int
> SALES_AMT money
>
> What I need to do is take each record from this, and create one record per
> number in the range, fitting the structure of:
> CURR_NBR int
> SALE_AMT money
>
> So for example, if the record were
>
> START_NBR = 1
> END_NBR = 3
> SIZE_OF_RANGE = 3
> SALES_AMT = 12.00
>
> I would want 3 records returned like:
> 1, 4.00
> 2, 4.00
> 3, 4.00
>
> I know I could do this if I joined to a table containing a range of
> integers, but I would rather do this "on the fly" than create a dummy table.
> I also tried creating a FUNCTION that returns an integer range that I could
> select from in the join. However, it will not allow me to use the START_NBR
> and END_NBR from the first table as the input values for the FUNCTION.
>
> If anyone knows of a clean way to do this, please let me know.

The numbers table doesn't need to be material. It can be a view ...

CREATE VIEW Digits AS
SELECT 0 AS num UNION ALL
SELECT 1 AS num UNION ALL
SELECT 2 AS num UNION ALL
SELECT 3 AS num UNION ALL
SELECT 4 AS num UNION ALL
SELECT 5 AS num UNION ALL
SELECT 6 AS num UNION ALL
SELECT 7 AS num UNION ALL
SELECT 8 AS num UNION ALL
SELECT 9 AS num
GO

CREATE VIEW Digits3 AS
SELECT A.num*100 + B.num*10 + C.num
FROM Digits A, Digits B, Digits C
GO

Then you can join ...

SELECT D.num, T.SalesAmount/T.Size_Of_Range
FROM Digits3 D, MyTable T
WHERE D.num BETWEEN T.StartNbr AND T.EndNbr

18 Aug 2006 9:14 PM

Tracy McKibben

McDale wrote:
Show quote

> Hey everyone,
>
> I have a situation where I need to take one record and split it up over a
> range of integers that could be different for each record. The result would
> be one record for each integer in that range. Here is an example:
>
> The table contains
> START_NBR int
> END_NBR int
> SIZE_OF_RANGE int
> SALES_AMT money
>
> What I need to do is take each record from this, and create one record per
> number in the range, fitting the structure of:
> CURR_NBR int
> SALE_AMT money
>
> So for example, if the record were
>
> START_NBR = 1
> END_NBR = 3
> SIZE_OF_RANGE = 3
> SALES_AMT = 12.00
>
> I would want 3 records returned like:
> 1, 4.00
> 2, 4.00
> 3, 4.00
>
> I know I could do this if I joined to a table containing a range of
> integers, but I would rather do this "on the fly" than create a dummy table.
> I also tried creating a FUNCTION that returns an integer range that I could
> select from in the join. However, it will not allow me to use the START_NBR
> and END_NBR from the first table as the input values for the FUNCTION.
>
> If anyone knows of a clean way to do this, please let me know.
>
> Thanks!
> Mike
>

What is your ultimate goal? I know you say you want to produce a table
with a row for each integer, but what will that table be used for?
There might be a way to accomplish that without creating this new table.
Otherwise, you're likely going to have to use a numbers table.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How can I pivot data on a range of integers? - Brain buster here!