- 3,722,875 Users
- 2,244,428 Discussions
- 7,850,121 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2K Databases
- 599 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 496 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 421 SQLcl
- 59 SQL Developer Data Modeler
- 185K SQL & PL/SQL
- 21.1K SQL Developer
- 2.4K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.6K Development Tools
- 12 DevOps
- 3K QA/Testing
- 327 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 71 Java Community Process
- 2 Java 25
- 11 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 14 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
Transposing Rows into Columns (Except the number of columns could vary)

I am trying to transpose rows into columns but my rows per student are not always the same because different students take different classes. I have created three tables where STUDENT_CLASS_TB is my table and I have dummy tables STUDENT_1001_TB and STUDENT_1002_TB to merely show how I want the data to be displayed by Student. My Query needs to work for only 1 Student at a time.
I am attaching Scripts:
I had to suffix file with .TXT as it did not like a .SQL extension.
Thanks in advance.
Best Answer
-
Hi,
Once again, this can be done in SQL or PL/SQL, but they are the wrong tools for this job. If your ultimate goal is to get some kind of output in Apex, then post a question in the Apex space:
There may be a completely different approach that is much simpler in AApex.
Here's one way you could do it in pure SQL, with SQL*Plus:
-- Preliminary Query SET FEEDBACK OFF SET PAGESIZE 0 SET TERMOUT OFF SPOOL c:\temp\course_list.sql SELECT DISTINCT CASE WHEN course = MIN (course) OVER () THEN ' ' ELSE ',' END || ' ''' || course || ''' AS course_' || SUBSTR (course, 1, 4) || '_hours' FROM student_class_tb WHERE student_id IN (&student_id_list) ORDER BY 1; SPOOL OFF -- Main Query SET FEEDBACK ON SET PAGESIZE 50 SET TERMOUT ON SELECT * FROM student_class_tb PIVOT ( SUM (course_hours) FOR course IN ( @c:\temp\course_list.sql ) ) WHERE student_id IN (&student_id_list) ORDER BY student_id, month_year;
This query works with one or more studetd_ids. &student_id_list can be either a single value or a comma-delimited list of several values. When &student_id = '1001', the output is:
This is not quite what you said you wanted. I assume the different numbers are due to typos.
As posted, this uses the first 4 characters of the course in the column headers, and the columns are in alphabetic order by those abbreviations. If you want some courses abbreviated to the only 3 characters (e.g., course_eng_hours instead of course_engl_hours) or the columns in some special order (e.g. course_eng_hours first) then that can be done. If you need help, post your requirements.
Answers
-
No prudent person would open attachments from an unknown source (sorry, but "Roxy rollers" is an unknown source). Some of the veteran members of this forum don't seem to have a problem with that - which only proves that they are not prudent.
3.14kB seems a bit large - how much data did you put in there? A few rows in each table, to illustrate your point but without becoming redundant, is all you need. And, at that point, you can simply post the text directly in your question - not as an attachment. I hope the "text" file includes CREATE TABLE and INSERT statements to re-create the test case - right?
In any case - pivoting into columns that depend on the input data is generally a poor practice. It can be done, using dynamic SQL (which is not a topic at the beginner level), and it is often done under the hood by reporting applications - so that you don't have to bother writing the dynamic SQL code yourself. In any case, the first and most basic question you must ask yourself (and your business user) is - do you really need to do this?
Also, just out of curiosity - are you the same poster as this one on StackOverflow? https://stackoverflow.com/questions/64972066/how-to-create-id-number-without-duplication#comment114867036_64972066
Or, perhaps, you are taking the same class together? Your questions are different, yet so similar - and they were posted on the same day. If it's just a coincidence, it is a big one! (Note: on SO, a few hours ago, I suggested the creation of a STUDENT_CLASS table, with that exact name...) 🤭
-
Hi,
Reporting tools have features for pivoting. Don't try doing it in SQL if you can use a tool made for this job.
If you must do it in SQL, then how is the output going to be used? If the output is just going to be read by people, then you don't actually need separate columns; you can produce output with a huge VARCHAR2 column that looks like separate columns. See:
If you really, really need a variable number of columns, then you need dynamic SQL.
-
....and (to continue from Frank), if you need variable numbers of columns, then how are you expecting to write any code to use the results of that query? If you don't know how many columns you are going to have, you won't know how many columns there are to reference or what their column names will be, so you can only use dynamic code to reference the results.... and when everything becomes dynamic, you're in the realms of code that is slow and breaks easily (and can be subject to security issues too).
Of course, if you know there are a "maximum" number of columns, such as "a student can't ever take more than 10 classes", then you can write static SQL instead of dynamic, to cater for that maximum number, and just have nulls where there is no data. At least then you'll have a known number of columns and known column names to reference the results.
Like mathguy said though, we need to see the details on the community please; I'm a veteran who certainly won't download documents that have been supplied from external sources, nomatter how well Oracle suggests they scan the uploads beforehand.
-
I apologize for my confusing request and by no means was I trying to spam anyone here. I initially wanted to post the CREATE table and INSERT statements but the formatting didn't look look good. I will do that now that I have also learnt how to better format on here. I am however not the same person on Stack Overflow.
CREATE table scripts:
create table student_class_tb(student_id varchar2(4), month_year date, course varchar2(10), course_hours number); create table student_1001_tb( student_id varchar2(4), month_year date, course_eng_hours number, course_math_hours number, course_phy_hours number, course_chem_hours number, course_bio_hours number ) create table student_1002_tb( student_id varchar2(4), month_year date, course_eng_hours number, course_hist_hours number, course_geog_hours number, course_civ_hours number )
INSERT statements for tables
insert into student_class_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 'English',40); insert into student_class_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 'Math',40); insert into student_class_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 'Physics',40); insert into student_class_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 'Chemistry',40); insert into student_class_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 'Biology',40); insert into student_class_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 'English',20); insert into student_class_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 'Math',20); insert into student_class_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 'Physics',20); insert into student_class_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 'Chemistry',20); insert into student_class_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 'Biology',20); insert into student_class_tb values ('1002',to_date('01-JAN-2020','DD-MON-YYYY'), 'English',40); insert into student_class_tb values ('1002',to_date('01-JAN-2020','DD-MON-YYYY'), 'History',40); insert into student_class_tb values ('1002',to_date('01-JAN-2020','DD-MON-YYYY'), 'Geography',40); insert into student_class_tb values ('1002',to_date('01-JAN-2020','DD-MON-YYYY'), 'Civics',40); insert into student_class_tb values ('1002',to_date('01-FEB-2020','DD-MON-YYYY'), 'English',30); insert into student_class_tb values ('1002',to_date('01-FEB-2020','DD-MON-YYYY'), 'History',30); insert into student_class_tb values ('1002',to_date('01-FEB-2020','DD-MON-YYYY'), 'Geography',30); insert into student_class_tb values ('1002',to_date('01-FEB-2020','DD-MON-YYYY'), 'Civics',30); insert into student_1001_tb values ('1001',to_date('01-JAN-2020','DD-MON-YYYY'), 40, 40, 40, 40, 40); insert into student_1001_tb values ('1001',to_date('01-FEB-2020','DD-MON-YYYY'), 20, 20, 20, 20, 40); insert into student_1002_tb values ('1002',to_date('01-JAN-2020','DD-MON-YYYY'), 40, 40, 40, 40); insert into student_1002_tb values ('1002',to_date('01-FEB-2020','DD-MON-YYYY'), 30, 30, 30, 30);
I have my data currently represented as they way it is in STUDENT_CLASS_TB by Student (Which is already a query in itself). My goal eventually is to use my Query in Oracle Reports (Graph) where I display the data as several "Series" using symbols. Currently, the way the data is represented results in a single "Series". That's why I was hoping represent the data like in STUDENT_1001_TB and STUDENT_1002_TB. I will have a maximum number of "columns" which is 10.
Once again, I apologize for the first post.
Thanks
-
Hi,
Once again, this can be done in SQL or PL/SQL, but they are the wrong tools for this job. If your ultimate goal is to get some kind of output in Apex, then post a question in the Apex space:
There may be a completely different approach that is much simpler in AApex.
Here's one way you could do it in pure SQL, with SQL*Plus:
-- Preliminary Query SET FEEDBACK OFF SET PAGESIZE 0 SET TERMOUT OFF SPOOL c:\temp\course_list.sql SELECT DISTINCT CASE WHEN course = MIN (course) OVER () THEN ' ' ELSE ',' END || ' ''' || course || ''' AS course_' || SUBSTR (course, 1, 4) || '_hours' FROM student_class_tb WHERE student_id IN (&student_id_list) ORDER BY 1; SPOOL OFF -- Main Query SET FEEDBACK ON SET PAGESIZE 50 SET TERMOUT ON SELECT * FROM student_class_tb PIVOT ( SUM (course_hours) FOR course IN ( @c:\temp\course_list.sql ) ) WHERE student_id IN (&student_id_list) ORDER BY student_id, month_year;
This query works with one or more studetd_ids. &student_id_list can be either a single value or a comma-delimited list of several values. When &student_id = '1001', the output is:
This is not quite what you said you wanted. I assume the different numbers are due to typos.
As posted, this uses the first 4 characters of the course in the column headers, and the columns are in alphabetic order by those abbreviations. If you want some courses abbreviated to the only 3 characters (e.g., course_eng_hours instead of course_engl_hours) or the columns in some special order (e.g. course_eng_hours first) then that can be done. If you need help, post your requirements.