How to realize Row and column Wrap in SQL
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.
