1

I have a table which is not normalized and I can't change the structure of the table. The table contains 6 columns to hold preferences namely preference_num1...preference_num6. I want to extract data from table so that it looks like there are 6 rows with one preference being in one row.

To make it clear, the output should be Id,semester_start,year_start,email,preference_num. And, each row would be broken into 6 rows .

Name Null Type ----------------------- -------- ------------- ID number (primary key) YEAR_START NUMBER SEMESTER_START VARCHAR2(8) EMAIL VARCHAR2(100) PREFERENCE_NUM_1 VARCHAR2(1) PREF_INSTITUTION_NAME_1 VARCHAR2(100) PREF_INSTITUTION_CODE_1 VARCHAR2(15) PREF_COURSE_TITLE_1 VARCHAR2(100) COURSE_CODE_1 VARCHAR2(15) OFFERING_LABLE_1 VARCHAR2(15) PREFERENCE_NUM_2 VARCHAR2(1) PREF_INSTITUTION_NAME_2 VARCHAR2(100) PREF_INSTITUTION_CODE_2 VARCHAR2(15) PREF_COURSE_TITLE_2 VARCHAR2(100) COURSE_CODE_2 VARCHAR2(15) OFFERING_LABLE_2 VARCHAR2(15) PREFERENCE_NUM_3 VARCHAR2(1) PREF_INSTITUTION_NAME_3 VARCHAR2(100) PREF_INSTITUTION_CODE_3 VARCHAR2(15) PREF_COURSE_TITLE_3 VARCHAR2(100) COURSE_CODE_3 VARCHAR2(15) OFFERING_LABLE_3 VARCHAR2(15) PREFERENCE_NUM_4 VARCHAR2(1) PREF_INSTITUTION_NAME_4 VARCHAR2(100) PREF_INSTITUTION_CODE_4 VARCHAR2(15) PREF_COURSE_TITLE_4 VARCHAR2(100) COURSE_CODE_4 VARCHAR2(15) OFFERING_LABLE_4 VARCHAR2(15) PREFERENCE_NUM_5 VARCHAR2(1) PREF_INSTITUTION_NAME_5 VARCHAR2(100) PREF_INSTITUTION_CODE_5 VARCHAR2(15) PREF_COURSE_TITLE_5 VARCHAR2(100) COURSE_CODE_5 VARCHAR2(15) OFFERING_LABLE_5 VARCHAR2(15) PREFERENCE_NUM_6 VARCHAR2(1) PREF_INSTITUTION_NAME_6 VARCHAR2(100) PREF_INSTITUTION_CODE_6 VARCHAR2(15) PREF_COURSE_TITLE_6 VARCHAR2(100) COURSE_CODE_6 VARCHAR2(15) OFFERING_LABLE_6 VARCHAR2(15) DATA_EXTRACTION_DATE DATE OFFER_DATE_1 DATE OFFER_RESPONSE_1 VARCHAR2(20) FINAL_OFFER_RESPONSE_1 VARCHAR2(20) OFFER_DATE_2 DATE OFFER_RESPONSE_2 VARCHAR2(20) FINAL_OFFER_RESPONSE_2 VARCHAR2(20) OFFER_DATE_3 DATE OFFER_RESPONSE_3 VARCHAR2(20) FINAL_OFFER_RESPONSE_3 VARCHAR2(20) OFFER_DATE_4 DATE OFFER_RESPONSE_4 VARCHAR2(20) FINAL_OFFER_RESPONSE_4 VARCHAR2(20) OFFER_DATE_5 DATE OFFER_RESPONSE_5 VARCHAR2(20) FINAL_OFFER_RESPONSE_5 VARCHAR2(20) OFFER_DATE_6 DATE OFFER_RESPONSE_6 VARCHAR2(20) FINAL_OFFER_RESPONSE_6 VARCHAR2(20)

Thanks

Improve this question
2
  • 1
    I would use one query for each preference and union all Commented Aug 20, 2012 at 3:25
  • saul672 - Isn't it better to query and then use union all (as in my second example below)? Even for an indexed table, why read the same data over and over again? Commented Aug 20, 2012 at 13:45

1 Answer 1

Reset to default
3

You might find more information about this if you search for UNPIVOT techniques or "oracle columns to rows".

One way of doing this is as below, which works well if you are inputting one (indexed) id. I am getting only 3 of the preferences to keep the example simple.

This is the basic idea.

select id, sem_start, year_start, pref_num1 from table_1 where id = input_id union all select id, sem_start, year_start, pref_num2 from table_1 where id = input_id union all select id, sem_start, year_start, pref_num3 from table_1 where id = input_id

You should use this query instead, which would be more performant, since we are querying by id only once.

with t1 as (select * from table_1 where id = id1) select id, sem_start, year_start, pref_num1 from t1 union all select id, sem_start, year_start, pref_num2 from t1 union all select id, sem_start, year_start, pref_num3 from t1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.