Mysql row-to-column (using if + sum)

Mysql uses sumif to perform row-to-column conversion (the row table data is as follows). First, we should consider how to create a column structure [SQL] viewplaincopySELECTNAMEAS name, IF (type language, score, 0) ASyuwen, IF (type mathematics, score, 0) ASshuxueFROMcdy_test after completing the preceding steps, you can see that you only need to group the results by name.

Mysql uses sumif to perform row-to-column conversion (the row table data is AS follows). First, we should consider how to create a column structure [SQL] view plaincopy SELECT NAME AS 'name', IF (type = 'China ', score, 0) AS yuwen, IF (type = 'mat', score, 0) AS shuxue FROM cdy_test after completing the preceding steps, you can see that you only need to group the results by name.

Mysql uses sum + if to convert rows to columns (the row table data is as follows)

First think about how to create a column Structure

[SQL]View plaincopy

1. SELECT
2. Name as 'name ',
3. IF (type = 'China', score, 0) AS yuwen,
4. IF (type = 'mat', score, 0) AS shuxue
5. FROM
6. Cdy_test

After completing the preceding steps, you can see that you only need to group the results by name and sum the values by column sum or use max to get the maximum value.
(Because it does not correspond to the records of the subject, the score must be 0 ).

[SQL]View plaincopy

1. SELECT
2. Name as 'name ',
3. SUM (IF (type = 'China', score, 0) AS yuwen,
4. SUM (IF (type = 'mat', score, 0) AS shuxue
5. FROM
6. Cdy_test
7. GROUP
8. NAME

Conclusion: if is used to create new columns and write the scores of non-corresponding disciplines to 0. sum or max and group by are used to ensure that the retrieved values are subject values.

Corresponding value, so that you can complete row-to-column conversion (of course, this write method does not consider the impact on performance)

Another method: http://blog.sina.com.cn/s/blog_4b5bc01101011ipw.html)

Mysql row-to-column function GROUP_CONCAT:
Select country, terminal, GROUP_CONCAT (distinct performance_id) from dmsp_youyanban_base group by country, terminal