1

I am newbie to Oracle but have worked on mysql previously. We have migrated the database from mysql to oracle and want help in this scenario.

I have table in the oracle 10g which is in the following format:

student_id  student_key student_value
----------  ----------- -------------
         1  name        john
         2  name        bill
         1  age         28
         2  age         26
         2  result      pass

now i want to create a pivot on it so i use the following query:

select student_id, decode(student_key, 'name', student_Value, null) as  studentName
from student_table

the output comes as

student_id studentName
---------- -----------
1          john
1          null
2          bill
2          null
2          null
Taryn
  • 242,637
  • 56
  • 362
  • 405
user2678738
  • 43
  • 1
  • 3
  • Possible duplicates: ["How to do Pivoting in Oracle 10g"](http://stackoverflow.com/questions/3386803/how-to-do-pivoting-in-oracle-10g), ["Oracle10g SQL pivot"](http://stackoverflow.com/questions/6963684/oracle10g-sql-pivot-help), ["Oracle: pivot (coalesce) some counts onto a single row?"](http://stackoverflow.com/questions/2169720/oracle-pivot-coalesce-some-counts-onto-a-single-row?rq=1) – ThinkJet Aug 14 '13 at 07:29

1 Answers1

2

Oracle 11 has specialized PIVOT functionality (a nice introduction is here), but 10g doesn't. Here's how to do it in 10g:

SELECT
  student_id,
  MAX(CASE WHEN student_key = 'name' THEN student_value END) AS StudentName,
  MAX(CASE WHEN student_key = 'age' THEN student_value END) AS Age,
  MAX(CASE WHEN student_key = 'result' THEN student_value END) AS Result
FROM myTable
GROUP BY student_id

As with the Oracle PIVOT command, you need to know the number of output columns ahead of time.

Also note that MAX is used because we're rolling rows up to columns, which means we have to group, which means we need an aggregate function, so why not MAX? If you use MIN instead of MAX you'll get the same results.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • Hi this worked fine. now while fetching the data i am converting the clob data to the varchar2 using dbms_lob.substr().since my clob data is more than 4000, it is failing with error. it works fine if i use the chain of dbms_lob.substr() for example select course_id,DBMS_LOB.SUBSTR(attribute_Value, 4000, 1) field1, DBMS_LOB.SUBSTR(attribute_Value, 4000, 4001) field1, DBMS_LOB.SUBSTR(attribute_Value, 4000, 8001) field1, DBMS_LOB.SUBSTR(attribute_Value, 4000, 12001) field1, how can i do it in one shot. – user2678738 Aug 16 '13 at 13:52
  • Getting the entire `CLOB` depends on your front-end data access package. What is your front end? Note that some front ends have little or no support for any type of LOB, so chunking up the value like you're doing might be the best option, even if it is a bit ugly :) – Ed Gibbs Aug 16 '13 at 14:09