SQL 2008 Split columns by condition

in the process of reporting to the Personnel Bureau, a difficult problem was encountered, and the customer requested that a column of data in the database be divided into multiple columns according to the conditions.

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

Customer requirements according to the province classification, Hebei province a column, Beijing City, a column of Tianjin, the rest for others, the target effect is as follows:

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 following results are performed:

The user's needs just want to inquire whether this person is in this province, do not want to know exactly which city of this governorate, so use replace function to upgrade SQL statement, 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 following results are performed:

In the process of solving the problem, we found that there is no good solution to this kind of problem on the net, so we will share our research results with you, hoping to help you solve the problems in the development process.