Home » SQL & PL/SQL » SQL & PL/SQL » Challenging task !!!(query help)
Challenging task !!!(query help) [message #318207] Tue, 06 May 2008 02:10 Go to next message
Guru01
Messages: 4
Registered: May 2008
Junior Member
we are having a table in following format

day | grpID | pktID
--------------------
sun | 1 | 001
sun | 1 | 002
sun | 1 | 003
sun | 2 | 007
sun | 2 | 008
sun | 2 | 009

mon | 1 | 001
mon | 1 | 002
mon | 1 | 003
mon | 2 | 007
mon | 2 | 008
mon | 2 | 009

tue | 1 | 001
tue | 1 | 002
tue | 1 | 003
tue | 2 | 007
tue | 2 | 010
-------------------

1. We have a combination of pkdIDs related with a specific grpID, for a particular day.
Ex: For Sunday, we have two combination list for grpID=1 is (001,002,003) and for group id = 2 is (007,008,009)


2. We need to get all the available combined pktid for each group id for all the days .

Eg the the expected result that is needed from the above table

(001,002,003)
(007,008,009)
(007,010)
Re: Challenging task !!!(query help) [message #318213 is a reply to message #318207] Tue, 06 May 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just a standard pivot query, no great challenge.

Regards
Michel
Re: Challenging task !!!(query help) [message #318216 is a reply to message #318213] Tue, 06 May 2008 02:25 Go to previous messageGo to next message
Guru01
Messages: 4
Registered: May 2008
Junior Member
Can you please give me an example !! because the combination should be distinct and different like

(001,002,003)
(007,008,009)
(007,010)

Re: Challenging task !!!(query help) [message #318217 is a reply to message #318216] Tue, 06 May 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Add "distinct".
For examples, search for "pivot".

Of course, if you have posted a test case, create table and insert statements for you example, maybe I'd posted a query but there I'm too lazy to build this test case.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns of result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Tue, 06 May 2008 02:30]

Report message to a moderator

Re: Challenging task !!!(query help) [message #318219 is a reply to message #318217] Tue, 06 May 2008 02:36 Go to previous messageGo to next message
Guru01
Messages: 4
Registered: May 2008
Junior Member
Adding 'Distinct' will not help to solve this problem .I tried earlier with this .

Since we have the packet id 007 with the same group id in ferent days ,Distinct will result in a following combination
007,008,009,010 which is not needed .

we need the result in following format as separate rows

007,008,009.
007,010

Thanks
Guru
Re: Challenging task !!!(query help) [message #318222 is a reply to message #318219] Tue, 06 May 2008 02:49 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Pay special attention to what Michel wrote:
Quote:
Of course, if you have posted a test case, create table and insert statements for you example, maybe I'd posted a query but there I'm too lazy to build this test case.
Previous Topic: Is function return null value?
Next Topic: Constraint names are stored in which table by default?
Goto Forum:
  


Current Time: Sun Feb 16 00:41:13 CST 2025