A typical method for Row-to-column conversion between SQL Server and Oracle

There are many online users.ArticleIt refers to row-to-column conversion, but most of them are directly pasted.CodeWhile ignoring the intermediate process, I thought about why it was implemented and made the following notes, which may be of no value to some people who understand it, I hope it will be helpful to those who do not understand it.

For some businesses, the storage of data in the table and its final grid performance are exactly the same as reversing the source table. At this time, we encountered the problem of converting rows into columns.Simplify ProblemsYou do not have to worry about the table design and SQL statements:

Assume that the SQL statement used is:

Select   [ Name ] , [ Times ] , [ Money ]
From   [ Test ] . [ DBO ] . [ People ]

 

This table stores gold coins owned by two people in different times (the age is fixed to three: Young, middle-aged, and old:

Zhang San owns 1000, 5000, and 800 gold coins in the young, middle-aged, and old age periods respectively;

Li Si owns 1200, 6000, and 500 gold coins respectively in the young, middle-aged, and old age periods.

Now we want to show the gold coins owned by the two in different stages in a table similar to the following:

Name	Young	Middle-aged	Old
Zhang San	1000	5000	800
Li Si	1200	6000	500

 

We are now considering using the simplest and most direct method to achieve this. In fact, the key is how to create columns that need to be added and set their values. Now we will create "young" columns, the key question is, how to set the value of this column? The legal logic should be like this: if this row is not in the "young" era, then the "money" is regarded as 0, how can we write SQL statements?

If you use SQL Server, you must use case:

 

Case [ Times ]   When   ' Young '   Then   [ Money ]   Else   0   End   As Young

Or

Case   When [ Times ] =   ' Young '   Then   [ Money ]   Else   0   End   As Young

 

If Oracle is used, the decode function is used. Decode (1 + 1, 3, 'error', 2, 'yes', 5, 'error ', 'values returned below all '). This function will return "yes". The specific usage is not described here. I believe you can understand the meaning of this formula, the sentence for creating the "young" column with decode is:The complete SQL statement is as follows:

Decode (times, ' Young ' , Money, 0 ) Young

 

Select   [ Name ] , [ Times ] , [ Money ] ,

Case [ Times ]   When   ' Young '   Then   [ Money ]   Else   0   End   As Young,
Case [ Times ]   When   ' Middle-aged '   Then   [ Money ]   Else   0   End   As Middle-aged,
Case [ Times ]   When   ' Old '   Then   [ Money ]   Else   0   End   As Old

From [Test].[DBO].[People] 

 

Now let's take a look at the execution results:

I believe that when we see this result, we all know what to do next, that is, grouping by name, and summing up the money in the three ages:

Select   [ Name ] , Sum ( [ Young ] ) As Young, Sum ( [ Middle-aged ] ) As Middle-aged, Sum ( [ Old ] ) As Old From
( Select   [ Name ] , [ Times ] , [ Money ] ,

Case [ Times ]   When   ' Young '   Then   [ Money ]   Else   0   End   As Young,
Case [ Times ]   When   ' Middle-aged '   Then   [ Money ]   Else   0   End   As Middle-aged,
Case [ Times ]   When   ' Old '   Then   [ Money ]   Else   0   End   As Old

from [ test ] . [ DBO ] . [ People ] ) T
group by [ name ]

Here, subqueries are used to make the logic clearer. In fact, subqueries are not needed. As for SQL statements in Oracle, except for decode, the rest are almost the same, I learned how to implement SQL Server only after implementing it in Oracle.

Finally, let's look at the results:

In fact, when columns are not fixed, for example, in addition to "young", "middle-aged", and "old", there are other unknown times. The Implementation ideas are basically the same, you only need to dynamically generate SQL statements.