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

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

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

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

Processing your response...

Discuss This Question: 4 &nbspReplies

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

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

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

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;

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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