SQL 2008 Split columns by condition

in the process of reporting for the Bureau of Personnel, encountered a thorny problem. Customer requests that a column of data in a database be divided into multiple columns according to the criteria.

For example, there are provinces in the database that have such a column of data

Customer requirements are categorized according to the province. A column of Hebei province, a row of Beijing, a column of Tianjin. The rest is for others. Target effects such as the following:

Manually-written SQL statements version Version1.0:

Select     Hebei = ParseName (Provice, (charindex (' Hebei ', Provice)),   Beijing = ParseName (Provice, (charindex (' Beijing ', Provice)) ),   Tianjin = ParseName (Provice, (charindex (' Tianjin ', Provice)),  other = (select Provice where charindex (' Hebei ', provice) = 0
   and charindex (' Beijing ', provice) = 0 and                            charindex (' Tianjin ', provice) = 0) from a

the results of the operation are as follows:

The need for a user is simply to inquire whether the person is in the province or not and to know in detail which city of the governorate. So the SQL statement is upgraded with the REPLACE function, SQL statement Version2.0 version:

Select     Hebei =replace (ParseName (Provice, (charindex (' Hebei ', Provice)), ParseName (Provice, (charindex (' Hebei ', Provice)) ), ' √ '),   Beijing =replace (ParseName (Provice, (charindex (' Beijing '), Provice)), ParseName (Provice, (charindex (' Beijing '), Provice) ), ' √ '),   Tianjin =replace (ParseName (Provice, (charindex (' Tianjin ', Provice)), ParseName (Provice, (charindex (' Tianjin '), Provice )), ' √ '),   other =replace ((select Provice where charindex (' Hebei ', provice) = 0 and   charindex (' Beijing ', provice) = 0 and                            CHARINDEX (' Tianjin ', provice) = 0),                 (select Provice where charindex (' Hebei ', provice) = 0 and   charindex (' Beijing ', provice) = 0< C8/>and charindex (' Tianjin ', provice) = 0),                  ' √ ') from a

the results of the operation are as follows:

In the process of solving this problem, it is found that there is no very good solution to this kind of problem on the Internet, so we will take out the results of our research and share it with you. We hope to help you solve the problems encountered in the development process.

SQL 2008 Split columns by condition