Set up a query to display the same results on multiple rows?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to set up a query that will give me a result set that lists results on
multiple rows depending on the value of a field [row#]

ex. If [row#] is 3, then I need the other information that I am running the
query for to show up 3 times on different rows in the results.

Is this possible?
 
You can do this with a Cartesian product.

This article explains how to set this up:
http://allenbrowne.com/ser-39.html

(The article is actually about generating a record for each of n labels, but
it's exactly the same as you are asking for at the query level.)
 
jtoupence said:
I need to set up a query that will give me a result set that lists results on
multiple rows depending on the value of a field [row#]
Click to expand...

CREATE TABLE Test
(row_ID INTEGER NOT NULL,
data_col VARCHAR(10) NOT NULL);

INSERT INTO Test VALUES (1, 'One');
INSERT INTO Test VALUES (2, 'Two');
INSERT INTO Test VALUES (3, 'Three');

SELECT T1.row_ID, T1.data_col
FROM Test AS T1
INNER JOIN Test AS T2
ON T1.row_ID >= T2.row_ID
ORDER BY T1.row_ID;
 
[email protected] said:
jtoupence said:
I need to set up a query that will give me a result set that lists results on
multiple rows depending on the value of a field [row#]
Click to expand...

SELECT T1.row_ID, T1.data_col
FROM Test AS T1
INNER JOIN Test AS T2
ON T1.row_ID >= T2.row_ID
ORDER BY T1.row_ID;
Click to expand...

This won't work with values out of sequence but I love the results!
Please ignore me :)
 
Back
Top