Display more than one row with the same result from a field

I need to show more than one result from each field in a table. I need to do this with only one SQL sentence, I don´t want to use a Cursor.

This seems silly, but the number of rows ma开发者_如何学Cy vary for each item. I need this to print afterwards this information as a Crystal Report detail.

Suppose I have this table:

idItem     Cantidad     <more fields>
--------  -----------
    1000         3
    2000         2
    3000         5
    4000         1

I need this result, using one only SQL Sentence:

1000
1000
1000
2000
2000
3000
3000
3000
3000
3000
4000

where each idItem has Cantidad rows.

Any ideas?

It seems like something that should be handled in the UI (or the report). I don't know Crystal Reports well enough to make a suggestion there. If you really, truly need to do it in SQL, then you can use a Numbers table (or something similar):

SELECT
    idItem
FROM
    Some_Table ST
INNER JOIN Numbers N ON
    N.number > 0 AND
    N.number <= ST.cantidad

You can replace the Numbers table with a subquery or function or whatever other method you want to generate a result set of numbers that is at least large enough to cover your largest cantidad.

Check out UNPIVOT (MSDN)

• Another example

If you use a "numbers" table that is useful for this and many similar purposes, you can use the following SQL:

select t.idItem
  from myTable t
       join numbers n on n.num between 1 and t.Cantidad
order by t.idTtem

The numbers table should just contain all integer numbers from 0 or 1 up to a number big enough so that Cantidad never exceeds it.

As others have said, you need a Numbers or Tally table which is just a sequential list of integers. However, if you knew that Cantidad was never going to be larger than five for example, you can do something like:

Select idItem
From Table
    Join    (
            Select 1 As Value
            Union All Select 2
            Union All Select 3
            Union All Select 4
            Union All Select 5
            ) As Numbers
        On Numbers.Value <= Table.Cantidad

If you are using SQL Server 2005, you can use a CTE to do:

With Numbers As
    (
    Select 1 As Value
    Union All
    Select N.Value + 1
    From Numbers As N
    )
Select idItem
From Table
    Join Numbers As N
        On N.Value <= Table.Cantidad
Option (MaxRecursion 0);