Shulou(Shulou.com)05/31 Report--

How do you do row and column transitions in SQL? In response to this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

The conversion between rows and columns is the conversion between the two display forms as shown in the following figure

row to column

Suppose we have the following table:

SELECT *FROM studentPIVOT ( SUM(score) FOR subject IN)

The SQL statement above gives you the following results

PIVOT is followed by an aggregate function to get the result. The subjects followed by FOR are the columns we want to convert. In this way, Chinese, Mathematics and English in the subjects will be converted into columns. What follows IN is the specific subject value.

Of course, we can also use CASE WHEN to get the same result, but it is a little more troublesome to write.

SELECT name, MAX( CASE WHEN subject ='CHINESE ' THEN score ELSE 0 END) AS "CHINESE", MAX( CASE WHEN subject ='MATHEMATICS' THEN score ELSE 0 END) AS "MATHEMATICS", MAX( CASE WHEN subject ='ENGLISH ' THEN score ELSE 0 END) AS "ENGLISH"FROM studentGROUP BY name

CASE WHEN can be used to get the same results as PIVOT, not as simple and intuitive as PIVOT.

column transition

Suppose we have the following table Student1

SELECT *FROM student1UNPIVOT ( score FOR subject IN ("Chinese","Mathematics","English"))

UNPIVOT gives the following results:

We can also use the following method to get the same result

SELECT NAME, 'CHINESE' AS subject , MAX("CHINESE") AS scoreFROM student1 GROUP BY NAMEUNIONSELECT NAME, 'MATHEMATICS' AS subject , MAX("MATHEMATICS") AS scoreFROM student1 GROUP BY NAMEUNIONSELECT NAME, 'ENGLISH' AS subject , MAX("ENGLISH") AS scoreFROM student1 GROUP BY NAME The answer to the question of how to implement row and column conversion in SQL is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts, you can pay attention to the industry information channel to learn more about relevant knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.