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

To convert a result set to a column, use the window function and the case clause. to convert a result set to one or more rows, use the case clause and Cartesian product.

As shown in

Figure 1 Table A figure 2 Table B Figure 3

 

Question: How to obtain the result set shown in Figure 3 based on table A and table B? First, evaluate the Cartesian product of table A and Table B, as shown below:

With newtable
(
Select * from
(Select * From) B as tableb,
(Select provincename from a) as tablea
Group by Fujian, Hunan, Zhejiang, provincename
)

Run the preceding SQL statement

Figure 4

Then, use the case statement to obtain the value of each provincename in Table B for judgment and obtain the corresponding cityname.

With newtable
(
Select * from
(Select * From) B as tableb,
(Select provincename from a) as tablea
Group by Fujian, Hunan, Zhejiang, provincename
),

Lasttable
(
Select
Provincename,
Case

When provincename = 'fujian 'Then Fujian
When provincename = 'hunan 'then Hunan
When provincename = 'zhejiang 'Then Zhejiang
Else''
End as cityname
From newtable
)

Select * From lasttable where cityname <>''
Group by provincename, cityname

Run the preceding SQL statement to obtain the result set 5.

Figure 5

The method for converting a column in the result set into multiple rows is similar to the above, and is not described in detail again.