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

Question

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 may 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?

Answer 1

Check out UNPIVOT (MSDN)

• Another example

Answer 2

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.

Answer 3

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.

Answer 4

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