Convert Row Column Data To Row By SQL Statement

Question

I current using oracle 11g here is problem that i face. I have a SQL statement show as below:

 SELECT A.LM_PERSON_ID
 ,A.LM_GRADE
 ,C.COURSE_STR
 ,Decode (A.LM_GRADE,'001','1','002','2','003','3') AS JOB_GRADE


 FROM PS_LM_PERSON_JOB A
,PS_LM_LPLN_LRN B 
,PS_LM_LPLN_DTL C 
,PS_LM_ENRLMT D
,PS_LM_CI_TBL E 


WHERE A.LM_EMPL_RCD='0'
AND A.LM_EFFSEQ=(SELECT Max(A1.LM_EFFSEQ) FROM PS_LM_PERSON_JOB A1 
WHERE A.LM_PERSON_ID=A1.LM_PERSON_ID AND A.EFFDT=A1.EFFDT 
AND A.LM_EMPL_RCD=A1.LM_EMPL_RCD)
AND A.LM_ACTIVE='Y'
AND A.EFFDT=(SELECT Max(A2.EFFDT) FROM PS_LM_PERSON_JOB A2 
WHERE A.LM_PERSON_ID=A2.LM_PERSON_ID AND A.LM_EMPL_RCD=A2.LM_EMPL_RCD 
AND A.LM_EFFSEQ=A2.LM_EFFSEQ) 
AND A.LM_PERSON_ID=B.LM_PERSON_ID (+)  
AND B.LM_LPLN_ID=C.LM_LPLN_ID (+) 
AND B.LM_PERSON_ID=C.LM_PERSON_ID (+)
AND C.LM_ENRLMT_ID=D.LM_ENRLMT_ID (+)  
AND D.LM_CI_ID=E.LM_CI_ID (+)
AND E.EFFDT=(SELECT Max(E1.EFFDT) FROM PS_LM_CI_TBL E1 WHERE E.LM_CI_ID=E1.LM_CI_ID)


ORDER BY A.LM_PERSON_ID

The output i would like to have was:

  JOB GRADE   COURSE_STR.A    COURSE_STR.B    COURSE_STR.C 
    1              5               3               1
    2              4               2               2
    3              1               1               1

Can someone mind to share how you able to do it? Thank you very much.

Sure~ PS_LM_PERSON_JOB A= All Person details such as age, nationality will be in this table. PS_LM_LPLN_LRN B = Specific course ID PS_LM_LPLN_DTL C = Table to store course details such as cost, training hours and so on. And course name in the COURSE_STR field in this table. PS_LM_ENRLMT D = Person who entitle with the selected course. PS_LM_CI_TBL E = Check person who entitle with the course have been completed or not. — goh6319
– goh6319, Commented Mar 19, 2013 at 9:06
Very sorry to said i only can provide you the field name. What else information you wish to know? — goh6319
– goh6319, Commented Mar 19, 2013 at 23:44
dude..i feel your table and column names are not straight forward so i want to know which table has wish column and it's name yar!! — Thiyagu ATR
– Thiyagu ATR, Commented Mar 20, 2013 at 4:47

André Neves · Accepted Answer · 2013-03-20 10:25:31Z

1

Take a look at PIVOT operator and see if it solves your problem.

If you had provided more information I could attempt at writing your query. Perhaps a simple example of the query result without transformation would be enough.

EDIT :

let's imagine the test table:

CREATE TABLE pivot_test (
  job_grade            NUMBER,
  course_str    VARCHAR2(1)
);

INSERT INTO pivot_test VALUES (1, 'A');
INSERT INTO pivot_test VALUES (1, 'B');
INSERT INTO pivot_test VALUES (1, 'C');
INSERT INTO pivot_test VALUES (1, 'A');
INSERT INTO pivot_test VALUES (1, 'C');
INSERT INTO pivot_test VALUES (2, 'A');
INSERT INTO pivot_test VALUES (2, 'B');
INSERT INTO pivot_test VALUES (2, 'C');
INSERT INTO pivot_test VALUES (2, 'A');
INSERT INTO pivot_test VALUES (3, 'A');
INSERT INTO pivot_test VALUES (3, 'A');
INSERT INTO pivot_test VALUES (3, 'A');
INSERT INTO pivot_test VALUES (3, 'A');
INSERT INTO pivot_test VALUES (3, 'A');
INSERT INTO pivot_test VALUES (3, 'B');
INSERT INTO pivot_test VALUES (3, 'B');
INSERT INTO pivot_test VALUES (3, 'A');
COMMIT;

Our test data looks like:

select * from pivot_test;

 JOB_GRADE COURSE_STR
---------- ----------
         1 A
         1 B
         1 C
         1 A
         1 C
         2 A
         2 B
         2 C
         2 A
         3 A
         3 A
         3 A
         3 A
         3 A
         3 B
         3 B
         3 A

17 rows selected

Now we apply the PIVOT operation, and obtain the result:

SQL> SELECT *
  2        FROM pivot_test
  3        PIVOT(COUNT(course_str) FOR course_str IN('A' AS "COURSE_STR.A",
  4                                                  'B' AS "COURSE_STR.B",
  5                                                  'C' AS "COURSE_STR.C"));

 JOB_GRADE COURSE_STR.A COURSE_STR.B COURSE_STR.C
---------- ------------ ------------ ------------
         1            2            1            2
         2            2            1            1
         3            6            2            0

SQL>

I used COUNT() as an aggregation function but it could be SUM(), MAX(), etc.

Hope this helps

edited Mar 20, 2013 at 10:25

answered Mar 19, 2013 at 10:02

André Neves

1,09611 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

goh6319 Over a year ago

This is the example of pivot that i try to create. Unfortunately, it give me error message. You may have a look.

goh6319 Over a year ago

SELECT * FROM ( SELECT A.LM_GRADE FROM PS_LM_PERSON_JOB A WHERE A.LM_EMPL_RCD='0' AND A.LM_EFFSEQ=(SELECT Max(A1.LM_EFFSEQ) FROM PS_LM_PERSON_JOB A1 WHERE A.LM_PERSON_ID=A1.LM_PERSON_ID AND A.EFFDT=A1.EFFDT AND A.LM_EMPL_RCD=A1.LM_EMPL_RCD) AND A.LM_ACTIVE='Y' AND A.EFFDT=(SELECT Max(A2.EFFDT) FROM PS_LM_PERSON_JOB A2 WHERE A.LM_PERSON_ID=A2.LM_PERSON_ID AND A.LM_EMPL_RCD=A2.LM_EMPL_RCD AND A.LM_EFFSEQ=A2.LM_EFFSEQ) )SRC PIVOT (Decode (LM_GRADE,'001','1','002','2','003','3') AS JOB_GRADE ) PIV

André Neves Over a year ago

I think you are not applying the PIVOT syntax correctly. You have to use an aggregation function like SUM() our COUNT(). Please check my edited answer for an example similar to what you want (I think).

André Neves Over a year ago

The easiest way is to wrap your query and use the PIVOT operator in the end. Alternatively, you can use it directly in the query but sometimes it makes it harder to read afterwards. Using the "wrap" method, imagine the last query I gave above. Now you could simply replace the SELECT * FROM pivot_test PIVOT ... by SELECT * FROM (your query) PIVOT ...

goh6319 Over a year ago

O ic ic~yup i knew how to use sub-query. Anyway thank you very much for your help. (^^)

|

Collectives™ on Stack Overflow

Convert Row Column Data To Row By SQL Statement

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related