Summary of Oracle row-column Conversion

If you want to sort out the FAQs in Oracle, I think the row-to-column conversion problem is inevitable. Today, I used some leisure time to sort out my understanding and experiences. Before proceeding to my summary, I repeatedly thought about the following question: what is Row-to-column conversion? Under what circumstances do I need to convert rows to columns? 1. What is Row-to-column conversion? Observe the changes before and after the row-to-column conversion, which can be interpreted

If you want to sort out the FAQs in Oracle, I think the row-to-column conversion problem is inevitable. Today, I used some leisure time to sort out my understanding and experiences. Before proceeding to my summary, I repeatedly thought about the following question: what is Row-to-column conversion? Under what circumstances do I need to convert rows to columns? 1. What is Row-to-column conversion? Observe the changes before and after the row-to-column conversion, which can be interpreted

If you want to sort out the FAQs in Oracle, I think the row-to-column conversion problem is inevitable. Today, I used some leisure time to sort out my understanding and experiences. Before proceeding to my summary, I repeatedly thought about the following question: what is Row-to-column conversion? Under what circumstances do I need to convert rows to columns?

1. What is Row-to-column conversion?

After observing the changes before and after the row-to-column conversion, we can interpret it as converting the multi-row data into the display of a single row (multiple) column. Note that the single row is a single row in each group. (If you have any misunderstanding, please correct me !)

2. Row-to-column type

There are many column-and-column conversion problems in Oracle. There are roughly two types in summary.

Type1:

Convert

This requirement is mostly about displaying simple statistical summary results, and the implementation is relatively simple.

Type2:

Convert

In contrast, this type of conversion is more complex. When you complete row-to-column conversion, you also need to aggregate a column.

Lab

There is now a Score table that stores the Student Score details.

Code-1: select sdtid, SNAME, WMSYS. WM_CONCAT (SCORE) AS "SCORE1, SCORE2, SCORE3"
FROM SCORE
Group by sdtid, SNAME;

Here we only use an Oralce built-in function WMSYS. WM_CONCAT () to complete the conversion of the Tyoe-1.

Code-2: select sdtid,
SNAME,
MAX (DECODE (COURSE, 'China', SCORE) language,
MAX (DECODE (COURSE, 'mat', SCORE) mathematics,
MAX (DECODE (COURSE, 'English ', SCORE) English
FROM SCORE
Group by sdtid, SNAME;

This is based on the conversion of the original COURSE column. COURSE has several values (Chinese, mathematics, and English) that directly determine the amount of our code.

This makes us unable to help but expand. If there are 100 subjects in COURSE, do we need to pile up code in a silly way? The answer is no. When we cannot or are too lazy to determine these groups (such as Chinese and mathematics), we can use the method of creating custom functions to dynamically generate code, A typical example of the general process of Row-to-column conversion is provided."Http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html? 93286.

3. Why are rows converted into columns?

Through the above elaboration and examples, we find that the row-to-column conversion is actually a conversion of the original table, in order to display and analyze data from another perspective. I personally think that its use in actual development needs is still relatively limited, because developers often come into contact with some paradigm tables, table definitions are quite standardized, and the data structure is quite reasonable. In the field of data warehouse, data mining and report development in the complex and massive data volumes are very promising.

Affirmation: you are welcome to study and communicate with each other. Do not blindly follow them. If you have incorrect or biased opinions or understandings in the blog post, I hope you can correct them! Thank you!