|
View previous topic :: View next topic
|
| Author |
Message |
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
Hi,
Suppose table T is as follows.
col1 col2
----- -----
A 2
B 3
How to write a query that displays
A 1
A 2
B 1
B 2
B 3
Thanks! |
|
| Back to top |
|
 |
nileshp
New User
Joined: 25 Feb 2006
Posts: 31
|
|
|
|
Hi,
Can you explain your requirement in detail? From your post I am not able to understand want you want? |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
Suppose table T is as follows.
| Code: |
col1 col2
----- -----
A 2
B 3
|
Since row1 has value 2 in col2 for A in col1, A is to be displayed twice. Similarly, B to be displayed thrice. And if possible, have an incrementing count also displayed to the right as shown below.
| Code: |
A 1
A 2
B 1
B 2
B 3 |
Hope it helps! |
|
| Back to top |
|
|
speedcnu
New User
Joined: 27 Jan 2004
Posts: 6
Location: Hyderabad
|
|
|
|
| Select * from table T order by col1,col2 |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| Order by just sorts the rows selected for display. It doesn't generate rows or change the display values. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
If you're willing to do 2 "passes" you could read all of the rows and as you read each one generate the necessary "new" rows in a temporary table. You could then read the temporary table and report the rows that you built to your specification. |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
Hi Dick,
I agree that more than one 'pass' will be needed. But how do I generate more than one row using SQL? In my example above, 'A' has to be displayed twice and 'B' to be displayed thrice.
Thanks! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi W,
I was focusing on the results rather than the method.
For mechanics, you could declare a cursor that was "SELECT c1, c2, etc FROM tbl1, FETCH all of the rows via the cursor, and as each row is FETCHed, insert the required incremental rows in the temporary table.
Once you exhaust all of the rows with the FETCH, close the cursor and do whatever you need to with the rows in the temporary table. |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
Dick,
If I decide to use cursor and a cobol program then it is easy where the row genaration part is essentially handled by the program. But I am interested in completing the operation using SQL only.
thanks! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi W,
Well, maybe someone will have an "SQL only" suggestion. So far, in a week, nobody has yet jumped in with one  A bit of "good" news is that if you were to look in some of the SqlServer or Oracle forums, they sling a bunch of SQL code and there may be something in one of those.
As i mentioned, my approach to something is to make sure i understand what the business need is and then i figure which approach to use. I try to avoid predetermining "how" a thing is to be done and focus on making sure that what is done completely fills the requirement (and is maintainable as well as not horribly wasting machine resources (like a cartesian product).
One other thought (and i personally apply it to lots of opportunities) is that while i may be able to figure out a "trick" with SQL or the sort or some other utility, what happens when the solution has been running for a while and there is an additional requirement added - one that cannot be met with the "trick" solution? Then, what could have been a trivial change, becomes a (re-)write rather than a simple enhancement.
Good luck and if i come across anything that looks like what you want, i'll post it  |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
Dick,
I understand your concerns about implementing such a solution as a short term fix.
Actually I no longer require it as part of my testing so it has become sort of a curiosity to check if it can be done.
And I found a solution from another forum. It involved a little bit of extra work but it was a SQL-only solution. Not sure if the forum rules forbid linking to another forum. So just quoting him here.
| Quote: |
if you don't already have one, start by creating an integers table
Code:
create table integers
(i integer not null primary key);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);
select yourtable.col1
, integers i
from yourtable
inner
join integers
on integers.i between 1 and yourtable.col2
order
by yourtable.col1
, integers i |
This inner join with a between clause is fantastic! I never knew one could use a range of values like that in JOIN. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Good find 
Thanx for posting what you found. I'll wager that someone else will have a similar challange and this may save them some time. |
|
| Back to top |
|
|
|
|
