How the **** do I do this?

Please forgive me if I'm being dense but I'm new to SQL.

How do I show a record more than once in the output of a select statement? There is a field in my table that contains a numeric value, I want to show the record this amount of times in my output. (if the value is 3 I want to see the record 3 times etc. - preferably displayed as 1 of 3, 2 of 3, 3 of 3)

How can I do this without creating temporary tables (restriction on the DB)?

Any help appreciated.

Hmm..as for a straight SQL solution, I'm not sure. I sure can't think of anyway to do it....If you are going to be displaying the results with PHP or some other language like PHP, it can be done easily. If this is the case, let me know and I'll whip something up for you....

The problem is basically caused by the fact that we're using a reporting tool (business objects) to display the data. This generates the simple bits of the SQL but allows you to modify the SQL if you want to add anything a bit more complex. The data needs to be displayed on one of these reports so it does have to be an SQL solution.

I've searched high and low for an answer on this...I just can't seem to come up with anything that doesn't utilize a temp table...what reporting software are you using? are you sure it's not possible to do this with the reporting software? If you have found an answer for this, please let me know what it is!

We have eventually got it to work, the solution is far from pretty though:

SELECT
ORDERS.OrderKey,
ORDERS.Door,
ORDERS.Route,
ORDERS.C_Company,
ORDERS.C_City,
ORDERS.ConsigneeKey,
ORDERS.StorerKey,
(SELECT COUNT(*) FROM LOC A WHERE A.LOC >= B.LOC) AS LabelNo
FROM LOC B, ORDERS
WHERE
(
ORDERS.ConsigneeKey != ' '
AND ORDERS.Route != '99 '
AND ORDERS.StorerKey = 'WHS '
AND ORDERS.Route != 'zz '
AND (SELECT COUNT(*) FROM LOC A WHERE A.LOC >= B.LOC) <= CONVERT(int, ORDERS.Route)
)
ORDER BY
ORDERS.OrderKey, LabelNo

We're only interested in the data from the ORDERS file but we bounce everything off the LOC file to give us our list of numbers (it could be any file that will always have a suitable number of records).

The following also worked in our 'play' environment but not in the live (Oracle vs SQL server):

SELECT
LABELS.DESCRIPTION,
Y,
LABELS.NOOFLABELS
FROM
LABELS,
(SELECT
COUNT(*) AS Y
FROM
LABELS A, LABELS B
WHERE
A.DESCRIPTION >= B.DESCRIPTION
GROUP BY
A.DESCRIPTION
ORDER BY 1)
WHERE Y <= LABELS.NOOFLABELS
ORDER BY LABELS.DESCRIPTION

It's a bit tidier but not a lot.

Hopefully you can make some sense of this and hopefully I won't need to do anything this weird again!

Thanks for your help and interest.

Interesting...thanks for letting us know how you did it!