Select Information from a Table

[getjbb]

I have a situation where a person can have more then one item ordered. I need to layout the information as follows:

Person Item Ordered Item Description
----------------------------------------------
1 1 of 2 Notepads
1 2 of 2 Rulers
2 1 of 1 Crayon
3 1 of 3 Pencils
.
.

The information is in the same table.
If anyone could help me it would be appreciated

getjbb

 

[KenCunningham]

Not being funny, but

Notepads + Rulers + Crayon + Pencils = Homework

to me at least, and as such isn't allowed in Tek-Tips.

I want to be good, is that not enough?

 

[getjbb]

I am not a student. I am a Programmer Analyst who need help in creating a select statement. I chose school supplies, because it was the first things I could think of. Trust me I am not a student and do not wish to be one at this time.

getjbb

 

[Dagon]

Of course, getjjb could work for a stationery company.

select person,
to_char(row_Number() over (partition by person order by item))||' of '||to_char(count(item) over (partition by person)) as item_ordered, item_desc
from items

 

[KenCunningham]

Fair enough. At least there was no mention of ACME widgets I guess ;-)

I want to be good, is that not enough?

 

[getjbb]

Thanks for believing in me and a solution. Have a nice day.

ponderena

 

[ptheriault]

Hey Dagon,
Check out his post here...

thread183-1391668

to bad for him, your solution, (which is very good) won't work on his dev server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Dagon]

Developing in SQL*Server but implementing in Oracle...

That is the most ludicrous thing I've ever heard. Did the person who came up with that idea get paid for it ?

 

[ptheriault]

I know, That is crazy!
getjbb is going to need to getjob after this project gets implemented.

- Paul
- If at first you don't succeed, find out if the loser gets anything.