Convert a result set using SQL -- convert a result set to multiple columns

The previous article describes how to convert a result set into a column. The following describes how to convert a result set into multiple columns.

Assume that the result set displayed in table 2 is converted to the result set shown in table 3.

Figure 1 Figure 2 Figure 3

As shown in figure 1, the SQL statement in Figure 2 is

Select provincename, cityname from citytable

Maybe the reader may want to use the idea mentioned in the previous article to convert result set 2 to result set 3. Let's try to execute the following SQL code

Select
Case when provincename = 'fujian 'then cityname else' 'end as 'fujian ',
Case when provincename = 'hunan 'then cityname else' 'end as 'hunan ',
Case when provincename = 'zhejiang 'then cityname else' 'end as 'zhejiang'
From citytable

The result is displayed as 4.

Figure 4

Then we calculate the maximum number of results returned by each case, that is, the aggregate function max (). The SQL statement is as follows:

Select
Max (case when provincename = 'fujian 'then cityname else' 'end) as 'fujian ',
Max (case when provincename = 'hunan 'then cityname else' 'end) as 'hunan ',
Max (case when provincename = 'zhejiang 'then cityname else' 'end) as 'zhejiang'
From citytable

Result 5:

Figure 5

Obviously, figure 4 shows Figure 5 because a max () is used to obtain the maximum value of each column, and the balanced data also has the maximum value. Obviously, only one row of data is displayed in the final result set, this is obviously not the result we want.

To get the result shown in 3, you can use the SQL window function to copy the SQL code.

With newtable as <br/> (<br/> select *, <br/> row_number () over (partition by provincename order by cityname) <br/> As rn from citytable <br/>) <br/> select <br/> MAX (case when provincename = 'fujian 'then cityname else' end) as 'fujian ', <br/> MAX (case when provincename = 'hunan 'then cityname else' end) as 'hunan ', <br/> MAX (case when provincename = 'zhejiang 'then cityname else' end) as 'zhejiang ', <br/> rn <br/> from newtable <br/> group by Rn

Next we will explain the preceding SQL statement.

1 ~ 6. Use the common table expression CTE (advantage: 1. It does not consume as much performance as a temporary table, nor is it as readable as a subquery. 2. Enhanced maintainability and efficiency) define a temporary dataset (which is described for the moment) and then reference this public table dataset in row 12th, for more information about CTE, visit other websites. The preceding SQL also involves the SQL Window Function row_number () over (partition by provincename order by cityname ).

The row_number () over (order by columnname) function is a new function added by sql2005 Based on SQL2000. It is used to sort and number columns Based on columnname. The number starts from 1.

For example:

Select *,
Row_number () over (order by cityname)
As rn from citytable

Run the preceding SQL statement to sort the result set by cityname and start with 1. The result set shown in 6 is displayed.

Figure 6

Partition by provincename is to partition the entire result set according to provincename (in this example, each province is divided into one partition), then sort different partitions by cityname, and rename them, the number starts from 1.

For more information about window functions, see the next article http://blog.csdn.net/bin_520_yan/archive/2010/10/31/5977571.aspx

Execute the following SQL statement

Select *,
Row_number () over (partition by provincename order by cityname)
As rn from citytable

Result 7:

Figure 7

Then, convert the result set to a column using the case when else end statement, and execute the following SQL statement.

With newtable
(
Select *,
Row_number () over (partition by provincename order by cityname)
As rn from citytable
)
Select
Case when provincename = 'fujian 'then cityname else' 'end as 'fujian ',
Case when provincename = 'hunan 'then cityname else' 'end as 'hunan ',
Case when provincename = 'zhejiang 'then cityname else' 'end as 'zhejiang ',
Rn
From newtable

Result 8:

Figure 8

After reading the above results, you may have an idea to put the lines with the same rn value together and group RN, provincename, and cityname, as shown below:

With newtable
(
Select *,
Row_number () over (partition by provincename order by cityname)
As rn from citytable
)
Select
Case when provincename = 'fujian 'then cityname else' 'end as 'fujian ',
Case when provincename = 'hunan 'then cityname else' 'end as 'hunan ',
Case when provincename = 'zhejiang 'then cityname else' 'end as 'zhejiang ',
Rn
From newtable
Group by RN, provincename, cityname

Run the preceding SQL statement. The result is 9.

Figure 9

You may have understood what to do next. Yes, next we need to find the value of the max () Aggregation Function for each group and group it by rn only. The following SQL statement

With newtable
(
Select *,
Row_number () over (partition by provincename order by cityname)
As rn from citytable
)
Select
Max (case when provincename = 'fujian 'then cityname else' 'end) as 'fujian ',
Max (case when provincename = 'hunan 'then cityname else' 'end) as 'hunan ',
Max (case when provincename = 'zhejiang 'then cityname else' 'end) as 'zhejiang ',
Rn
From newtable
Group by Rn

Execute the preceding SQL statement to get the expected result 3!