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

Roxy rollers · November 2020

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:

student_tables_23NOV2020.txt

I had to suffix file with .TXT as it did not like a .SQL extension.

Thanks in advance.

Frank Kulash · November 2020

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:

APEX — oracle-tech

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.

mathguy · November 2020

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

Frank Kulash · November 2020

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.

BluShadow · November 2020

....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 · November 2020

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

Frank Kulash · November 2020

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:

APEX — oracle-tech

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.

Forum Stats

Howdy, Stranger!

Quick Links

Categories

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

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

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

Best Answer

Answers