Home » SQL & PL/SQL » SQL & PL/SQL » Help in Pivot Query (merged)
Help in Pivot Query (merged) [message #379770] Wed, 07 January 2009 22:45 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

My Pivot Sql is like this

SELECT a.lic_No,
MAX(DECODE(b.Job_Code,'T0102',d.Inv_No)) "SVC-1",
MAX(DECODE(b.Job_Code,'T0103',d.Inv_No)) "SVC-2",
MAX(DECODE(b.Job_Code,'T0104',d.Inv_No)) "SVC-3",
MAX(DECODE(b.Job_Code,'T0105',d.Inv_No)) "SVC-4",
MAX(DECODE(b.Job_Code,'T0106',d.Inv_No)) "SVC-5",
MAX(DECODE(b.Job_Code,'T0107',d.Inv_No)) "SVC-6"
FROM mss_ro_hdr a,
mss_ro_Jobs b,
mss_Inv_hdr d,
css_Car_Records e,
mss_Inv_Lines f
WHERE a.Id = b.rohd_Id
AND b.Id = f.rojb_Id
AND f.Inhd_Id = d.Id
AND a.Card_Id = e.Id
GROUP BY a.lic_No

Now I have a problem. My job_code repeated more than two times and now I want the inv_no to be displayed for all the job_codes.

For example if T0102 is used two times I want the inv_no to be showed for both.

Now in this query it is showing the latest inv_no only not the earlier one. Can anyone help me?

For example my output can be like this

LIC_NO SVC_1 SVC_2 SVC_3 SVC_4 SVC_5 SVC_6
XD1234 SAI10 SAI15 SAI20 SAI25 SAI30 SAI35
XD1234 SAI45 SAI50

My Current output is like this:
LIC_NO SVC_1 SVC_2 SVC_3 SVC_4 SVC_5 SVC_6
XD1234 SAI45 SAI50 SAI20 SAI25 SAI30 SAI35

Can you see the difference between two ouput? Anyway to display the required output.

Re: Help in Pivot Query [message #379772 is a reply to message #379770] Wed, 07 January 2009 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.
Help in Pivot Query - After formatted [message #379775 is a reply to message #379770] Wed, 07 January 2009 23:22 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
SELECT   a.lic_No,
         MAX(DECODE(b.Job_Code,'T0102',d.Inv_No)) "SVC-1",
         MAX(DECODE(b.Job_Code,'T0103',d.Inv_No)) "SVC-2",
         MAX(DECODE(b.Job_Code,'T0104',d.Inv_No)) "SVC-3",
         MAX(DECODE(b.Job_Code,'T0105',d.Inv_No)) "SVC-4",
         MAX(DECODE(b.Job_Code,'T0106',d.Inv_No)) "SVC-5",
         MAX(DECODE(b.Job_Code,'T0107',d.Inv_No)) "SVC-6"
FROM     mss_ro_hdr a,
         mss_ro_Jobs b,
         mss_Inv_hdr d,
         css_Car_Records e,
         mss_Inv_Lines f
WHERE    a.Id = b.rohd_Id
         AND b.Id = f.rojb_Id
         AND f.Inhd_Id = d.Id
         AND a.Card_Id = e.Id
GROUP BY a.lic_No


Now I have a problem. My job_code repeated more than two times and now I want the inv_no to be displayed for all the job_codes.

For example if T0102 is used two times I want the inv_no to be showed for both.

Now in this query it is showing the latest inv_no only not the earlier one. Can anyone help me?

For example my output can be like this

LIC_NO SVC_1 SVC_2 SVC_3 SVC_4 SVC_5 SVC_6
XD1234 SAI10 SAI15 SAI20 SAI25 SAI30 SAI35
XD1234 SAI45 SAI50

My Current output is like this:
LIC_NO SVC_1 SVC_2 SVC_3 SVC_4 SVC_5 SVC_6
XD1234 SAI45 SAI50 SAI20 SAI25 SAI30 SAI35

Can you see the difference between two ouput? Anyway to display the required output.
Re: Help in Pivot Query - After formatted [message #379777 is a reply to message #379775] Wed, 07 January 2009 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

>Can you see the difference between two ouput?
I can see that posted code did NOT produce the purported results!

The code contains "SVC-1" (dash character) while the faked "results" contain underscore characters.
Pivot Query with Table script & Sample data [message #379791 is a reply to message #379775] Thu, 08 January 2009 00:04 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
CREATE TABLE mss.mss_Test (
  lic_No   VARCHAR2(10),
  Job_Code VARCHAR2(5) NOT NULL,
  Inv_No   VARCHAR2(9))


SET DEFINE OFF;

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0102',
            'SAI065866');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0103',
            'SAI068878');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0104',
            'SAI072018');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0104',
            'SAI089251');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0102',
            'SAC016316');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0103',
            'SAI086146');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0105',
            'SAI075268');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1226E',
            'T0107',
            'SAI078779');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0102',
            'SAI066211');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0103',
            'SAI068679');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0102',
            'SAI085160');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0105',
            'SAI073110');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0106',
            'SAI076052');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1331J',
            'T0107',
            'SAI076863');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0107',
            'SAD007699');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0105',
            'SAI072328');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0102',
            'SAI086149');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0102',
            'SAI065835');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0102',
            'SAI065836');

INSERT INTO mss_Test
           (lic_No,
            Job_Code,
            Inv_No)
VALUES     ('GBA1396Y',
            'T0103',
            'SAI068173');

COMMIT;


Can you give me the required output now?
Re: Help in Pivot Query - After formatted [message #379802 is a reply to message #379775] Thu, 08 January 2009 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Can you give me the required output now?

FROM mss_ro_hdr a,
mss_ro_Jobs b,
mss_Inv_hdr d,
css_Car_Records e,
mss_Inv_Lines f

Your code involves 5 tables & you provide SQL for only 1 table.
How can anyone help you when you do not provide provide sufficient details to reproduce what you claim happens?

You are not making it easy to help you.
Re: Help in Pivot Query - After formatted [message #379821 is a reply to message #379802] Thu, 08 January 2009 00:57 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Sorry for inconvenience. Now only one table mss_test. The create script is there and insert datas are there and my current sql.

SELECT   LIC_NO,
         MAX(DECODE(JOB_CODE,'T0102',INV_NO)) "SVC-1",
         MAX(DECODE(JOB_CODE,'T0103',INV_NO)) "SVC-2",
         MAX(DECODE(JOB_CODE,'T0104',INV_NO)) "SVC-3",
         MAX(DECODE(JOB_CODE,'T0105',INV_NO)) "SVC-4",
         MAX(DECODE(JOB_CODE,'T0106',INV_NO)) "SVC-5",
         MAX(DECODE(JOB_CODE,'T0107',INV_NO)) "SVC-6"
FROM     MSS_TEST
GROUP BY LIC_NO


Re: Help in Pivot Query - After formatted [message #379824 is a reply to message #379821] Thu, 08 January 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your data your query gives:
SQL> SELECT   LIC_NO,
  2           MAX(DECODE(JOB_CODE,'T0102',INV_NO)) "SVC-1",
  3           MAX(DECODE(JOB_CODE,'T0103',INV_NO)) "SVC-2",
  4           MAX(DECODE(JOB_CODE,'T0104',INV_NO)) "SVC-3",
  5           MAX(DECODE(JOB_CODE,'T0105',INV_NO)) "SVC-4",
  6           MAX(DECODE(JOB_CODE,'T0106',INV_NO)) "SVC-5",
  7           MAX(DECODE(JOB_CODE,'T0107',INV_NO)) "SVC-6"
  8  FROM     MSS_TEST
  9  GROUP BY LIC_NO;
LIC_NO     SVC-1     SVC-2     SVC-3     SVC-4     SVC-5     SVC-6
---------- --------- --------- --------- --------- --------- ---------
GBA1226E   SAI065866 SAI086146 SAI089251 SAI075268           SAI078779
GBA1331J   SAI085160 SAI068679           SAI073110 SAI076052 SAI076863
GBA1396Y   SAI086149 SAI068173           SAI072328           SAD007699

3 rows selected.

And what do you want?

Regards
Michel
Re: Help in Pivot Query (merged) [message #379854 is a reply to message #379770] Thu, 08 January 2009 02:42 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Mr.Michel,

There are repeat job_code more than once. The repeated job_code I wanted in the second line along with the inv.no. If you see my table you can understand.

LIC_NO SVC-1 SVC-2 SVC-3 SVC-4 SVC-5 SVC-6
---------- --------- --------- --------- --------- --------- ---------
GBA1226E SAI065866 SAI086146 SAI089251 SAI075268 SAI078779
GBA1226E SAC.....
GBA1331J SAI085160 SAI068679 SAI073110 SAI076052 SAI076863
GBA1396Y SAI086149 SAI068173 SAI072328 SAD007699


Now you can understand I think.
Re: Help in Pivot Query (merged) [message #379857 is a reply to message #379854] Thu, 08 January 2009 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You then have to number your repeated values:
SQL> with 
  2    data as (
  3      select lic_no, job_code, inv_no,
  4             row_number() over (partition by lic_no, job_code order by null) rn
  5      from MSS_TEST
  6    )
  7  SELECT   LIC_NO,
  8           MAX(DECODE(JOB_CODE,'T0102',INV_NO)) "SVC-1",
  9           MAX(DECODE(JOB_CODE,'T0103',INV_NO)) "SVC-2",
 10           MAX(DECODE(JOB_CODE,'T0104',INV_NO)) "SVC-3",
 11           MAX(DECODE(JOB_CODE,'T0105',INV_NO)) "SVC-4",
 12           MAX(DECODE(JOB_CODE,'T0106',INV_NO)) "SVC-5",
 13           MAX(DECODE(JOB_CODE,'T0107',INV_NO)) "SVC-6"
 14  FROM data
 15  GROUP BY LIC_NO, rn
 16  ORDER BY LIC_NO, rn
 17  /
LIC_NO     SVC-1     SVC-2     SVC-3     SVC-4     SVC-5     SVC-6
---------- --------- --------- --------- --------- --------- ---------
GBA1226E   SAI065866 SAI086146 SAI072018 SAI075268           SAI078779
GBA1226E   SAC016316 SAI068878 SAI089251
GBA1331J   SAI085160 SAI068679           SAI073110 SAI076052 SAI076863
GBA1331J   SAI066211
GBA1396Y   SAI065836 SAI068173           SAI072328           SAD007699
GBA1396Y   SAI086149
GBA1396Y   SAI065835

7 rows selected.

Regards
Michel

[Updated on: Thu, 08 January 2009 02:56]

Report message to a moderator

Re: Help in Pivot Query (merged) [message #380003 is a reply to message #379857] Thu, 08 January 2009 18:59 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Thank you very much Mr.Michel
Not working in Oracle 8i [message #380045 is a reply to message #380003] Thu, 08 January 2009 22:06 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

Mr.Michel gave me but that command is not working in Oracle 8i. Any way is there?
Re: Help in Pivot Query (merged) [message #380046 is a reply to message #379770] Thu, 08 January 2009 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.
From above "Post exact Database (4 decimal places, please) and OS version. By doing so, you'll avoid reactions using features you can't use."

>Any way is there?

Upgrade to a version of Oracle that has been supported during the current century.

[Updated on: Thu, 08 January 2009 22:41]

Report message to a moderator

Re: Help in Pivot Query (merged) [message #380051 is a reply to message #380046] Thu, 08 January 2009 23:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, if one know the solution of the problem, then he/she should reply.
He/she is not required to say "Change your problem"

regards,
Delna
Re: Not working in Oracle 8i [message #380059 is a reply to message #380045] Fri, 09 January 2009 00:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The with clause (sub-query factoring clause) was not introduced until 9i, so you will need to move the sub-query into the from clause as an inline view. This is an example of why it helps both you and us if you provide your exact version in advance. Hopefully, when you say 8i, you mean 8.1.6, not 8.1.5, as the row_number analytic function wasn't introduced until 8.1.6. Also, 8i is no longer supported, so you should definitely upgrade.

SELECT   LIC_NO,
         MAX(DECODE(JOB_CODE,'T0102',INV_NO)) "SVC-1",
         MAX(DECODE(JOB_CODE,'T0103',INV_NO)) "SVC-2",
         MAX(DECODE(JOB_CODE,'T0104',INV_NO)) "SVC-3",
         MAX(DECODE(JOB_CODE,'T0105',INV_NO)) "SVC-4",
         MAX(DECODE(JOB_CODE,'T0106',INV_NO)) "SVC-5",
         MAX(DECODE(JOB_CODE,'T0107',INV_NO)) "SVC-6"
FROM    (select lic_no, job_code, inv_no,
                row_number() over 
                  (partition by lic_no, job_code     
                   order by null) rn
         from MSS_TEST)
GROUP BY LIC_NO, rn
ORDER BY LIC_NO, rn
/

Current Version 8.1.7.0.0 [message #380409 is a reply to message #380059] Sun, 11 January 2009 20:51 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

Actually I recommend my company to upgrade but since due to this financial crisis we are unable to spend money to upgrade our system.

So can anyone help to find a solution for this in 8.1.7.0.0.
Re: Current Version 8.1.7.0.0 [message #380441 is a reply to message #380409] Sun, 11 January 2009 23:29 Go to previous message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The solution that I provided should work in 8.1.7. Did you test it?

UPDATE: I just read your email saying that it didn't work. I believe it should and suspect that you did something wrong, so please post a copy and paste of a complete run with line numbers and results, using the sample data previously provided.

[Updated on: Sun, 11 January 2009 23:34]

Report message to a moderator

Previous Topic: How to Populate a fact table from dimension tables.Kindly reply.
Next Topic: Root cause for the white space while fetching the value from ORACLE
Goto Forum:
  


Current Time: Fri Dec 02 16:22:39 CST 2016

Total time taken to generate the page: 0.26557 seconds