Oracle SQL – convert rows in a table to columns in a query

pts.
Tags:
Oracle
SQL
Please forgive me for being longwinded. I am trying to pull data from 2 tables, there is a courseid field that is the primary key linking the tables. The first table contains the demographic and course name, the second table contains some detail information for the course - mainly number of sections in the user completed. I am working on pulling a query for a single course that has 4 sections. Each section has its own row in the second table. I want to pull a single row that has the demographic and columns for sections 1-4 if they have been completed. Right now I have 4 rows of demographic info with each section in a column. I have been unable to get any type of grouping to work to make it into 1 row. Any help would be greatly appreciated. Thanks Aaron

Answer Wiki

Thanks. We'll let you know when a new response is added.

Although there are some bizzarre single queries that can do this for you, your best bet is to write a query that matches the course record with detail records in the normal manner, then to read through this query, either using FETCH or FOR, depending on what language you’re writing in, and then programmatically forming the four columns. Depending on what you’re going to do next, process the accumulated data, or insert the accumulated data into a table.

Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Welcome
    Hi Aaron, Supposing you can distingush the 4 rows for the 4 sections by 4 distinct values, let's say '1','2','3','4' in a "section_id" column, then you can produce 1 row with 4 columns for each "course_id" by using the following group by: SELECT t1.course_id, max(decode(t2.section_id,'1',t2.data_col,null)) as col_section1, max(decode(t2.section_id,'2',t2.data_col,null)) as col_section2, max(decode(t2.section_id,'3',t2.data_col,null)) as col_section3, max(decode(t2.section_id,'4',t2.data_col,null)) as col_section4 from course_table t1, section_table t2 where t2.course_id = t1.course_id group by t1.course_id / Hope this could hep. Best Regards, Iudith
    0 pointsBadges:
    report
  • Gristle
    Have you tried joining against the second table multiple times? See this example: select * from first COURSEID ----------- 100 101 102 select * from second COURSEID SECTION DATA ----------- ----------- ---------- 100 1 100.1 100 2 100.2 100 3 100.3 100 4 100.4 101 1 101.1 101 2 101.2 101 3 101.3 101 4 101.4 102 1 102.1 102 4 102.4 Now to second onto first 4 times (one per section) using outer joins to ensure all rows from first are shown (Nulls appearing for second where there is no data) select a.courseid, one.section, one.data, two.section, two.data, three.section, three.data, four.section, four.data from first a left outer join second one on a.courseid=one.courseid and one.section=1 left outer join second two on a.courseid=two.courseid and two.section=2 left outer join second three on a.courseid=three.courseid and three.section=3 left outer join second four on a.courseid=four.courseid and four.section=4 COURSEID SECTION DATA SECTION DATA SECTION DATA SECTION DATA -------- ------- ----- ------- ----- ------- ----- ------- ----- 100 1 100.1 2 100.2 3 100.3 4 100.4 101 1 101.1 2 101.2 3 101.3 4 101.4 102 1 102.1 - - - - 4 102.4 Presto! One row per course id, including the data from each of the four sections. Hope this is of help! Russell
    0 pointsBadges:
    report
  • Structsound
    Use the analytic function LAG or LEAD to look ahead (or behind) and pivot the data from rows to columns in the detail table, then join the results with the courses table. Although the queries seem complex, this will give you best performance. SELECT A.*, B.SECTION1, B.SECTION2, B.SECTION3, B.SECTION4 FROM courses a, (SELECT courseid, lead(section_info,0) OVER (PARTITION BY courseid ORDER BY section_info) section1, lead(section_info,1) OVER (PARTITION BY courseid ORDER BY section_info) section2, lead(section_info,2) OVER (PARTITION BY courseid ORDER BY section_info) section3, lead(section_info,3) OVER (PARTITION BY courseid ORDER BY section_info) section4 FROM section_details ) b WHERE a.courseid = b.courseid;
    0 pointsBadges:
    report
  • Aaron25177
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-sql-convert-rows-in-a-table-to-columns-in... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following