Convert a result set using SQL -- reversely transpose a result set to a column

The previous four articles about how to use SQL to convert the result set may be a headache when the rows are converted into columns, but they can still be solved after finding a solution. One of them is, for the N rows in each group, assuming that the n rows must be in the same row in the result set together, we try to make a column of the N rows in each group (assuming RN) the values are the same, which can be implemented using the window function row_number (), so that you only need to group by rn plus Aggregate functions (such as Max, then, judge the value of the RN column in case. The Cartesian product is directly used for column-to-row conversion, so that each row in the original table (assuming that there are m columns) has m rows, next, let the m row be able to partition based on one of the two columns (one of which is the partition, and the other is the row_number () over (order by column name) as RN ), each line has a different rn value, so that the final combination can be performed according to the case clause.

In general, we only need to use the window function (row_number () over (partition by column1 order by column2) and the case clause for Row-to-column conversion, the cartesian products and case clauses are used for column-to-row.

The following describes a special case where all rows are converted into a column and the result set shown in Figure 1 is converted to the result set shown in figure 2.

Figure 1 Figure 2

According to the introduction in the previous chapters, we will convert the result set in figure 1 to the result set in figure 2, which is to convert rows into columns. We can use cartesian products and case clauses to solve this special type of Row-to-column conversion.

First, perform the Cartesian operation on the result set in Figure 1, so that each row is changed to the corresponding three rows. The following SQL statement:

Select provincename, cityname, citypopulation from citytable,
(Select top 3 ID from citytable) as Table1

Run the preceding SQL statement to obtain the result set shown in 3.

Figure 3

Now we want to partition each provincename and sort all citynames in each provincename. The following SQL statement

With tableone
(
Select provincename, cityname, citypopulation from citytable,
(Select top 3 ID from citytable) as Table1
)
Select *, row_number () over (partition by cityname order by cityname) as rn from tableone

Result 4:

Figure 4

Next, use the case clause to determine the RN value. The result is displayed.

With tableone
(
Select provincename, cityname, citypopulation from citytable,
(Select top 3 ID from citytable) as Table1
)
, Tabletwo
(
Select *, row_number () over (partition by cityname order by cityname) as rn from tableone
)
Select
Case when Rn = 1 then provincename
When Rn = 2 then cityname
When Rn = 3 then cast (citypopulation as char (10 ))
Else''
End as 'result'
From tabletwo
Order by provincename

Run the preceding SQL statement to obtain the result 5.

Figure 5

Some problems encountered in this process are as follows:

I. error message: Unless top or for XML is also specified, the order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions.

The SQL statement is as follows:

With tableone as <br/> (<br/> select provincename, cityname, citypopulation from citytable, <br/> (select top 3 ID from citytable) as Table1 <br/>) <br/>, tabletwo as <br/> (<br/> select *, row_number () over (partition by cityname order by cityname) as rn from tableone <br/> order by provincename <br/>) <br/> select <br/> case when Rn = 1 then provincename <br/> when Rn = 2 then cityname <br/> when Rn = 3 then cast (citypopulation as char (10 )) <br/> else ''<br/> end as 'result' <br/> from tabletwo <br/> -- order by provincename

Place order by provincename of row 18th in row 9th. Cause of error: Select for a table does not actually return a table, but a cursor; As a subquery, if order ....., this subquery must contain a top keyword. There are two solutions:

First: Add a top keyword statement to the subquery: Top 100 percent, which is about to change the 8th rows:

Select Top 100 percent *, row_number () over (partition by cityname order by cityname) as rn from tableone

Second, delete the order by statement in the subquery and place the order by statement in the external query statement.

2. An error occurs in the case clause in the following SQL statement. The information is as follows: An error occurred while converting the nvarchar value 'hunan 'to a data type Int.With tableone as <br/> (<br/> select provincename, cityname, citypopulation from citytable, <br/> (select top 3 ID from citytable) as Table1 <br/>) <br/>, tabletwo as <br/> (<br/> select *, row_number () over (partition by cityname order by cityname) as rn from tableone <br/>) <br/> select <br/> case when Rn = 1 then provincename <br/> when Rn = 2 then cityname <br/> when Rn = 3 then citypopulation <br/> else ''<br/> end as 'result' <br/> from tabletwo <br/> order by provincename

In the preceding case clause, provincename and cityname are of the optimized type, while citypopulation is of the integer type. Therefore, you must convert citypopulation to the optimized type, change the Code in line 13th to when Rn = 3 then cast (citypopulation as char (10.