Forum Stats

  • 3,750,794 Users
  • 2,250,248 Discussions
  • 7,867,147 Comments

Discussions

Transposing Rows into Columns (Except the number of columns could vary)

Roxy rollers
Roxy rollers Member Posts: 67 Red Ribbon

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.

Tagged:

Best Answer

Answers

  • mathguy
    mathguy Member Posts: 9,975 Gold Crown

    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...) 🤭

    BluShadowRoxy rollers
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,899 Red Diamond

    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:

    PIVOT SQL — oracle-tech

    If you really, really need a variable number of columns, then you need dynamic SQL.

    Roxy rollers
  • BluShadow
    BluShadow Member, Moderator Posts: 41,298 Red Diamond

    ....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.

    Roxy rollers
  • Roxy rollers
    Roxy rollers Member Posts: 67 Red Ribbon

    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