Ms SQL server tree structure data display SQL statement (pure SQL statement, no function)

Select dpcode1 + dpcode2 + dpcode3 as department code, dpname1 + dpname2 + dpname3 as department name from t_dpt where dpname1 is not null and dpname2 is not null and dpname3 is not null Union all select dpcode1 + dpcode2 + dpcode3 as department code, dpname1 + dpname2 as department name from t_dpt where dpname1 is not null and dpname2 is not null and dpname3 is null Union all select dpcode1 + dpcode2 + dpcode3 as department code, dpname1 as department name from t_dpt where dpname1 is not null and dpname2 is null and dpname3 is null;

Running result:

Department Code Department name

01 foreign languages

0102 foreign languages and Japanese Majors

0102001 class 0331 for Japanese majors in foreign languages

0103 Foreign Business English

0103001 Foreign Business English class 0341

0104 Foreign Language Instructors

0103002 Foreign Business English class 0242

Appendix:

Basic Tables Used

Create Table [DBO]. [t_dpt] (
[Dpcode1] [char] (2) Collate chinese_prc_ci_as not null,
[Dpcode2] [char] (2) Collate chinese_prc_ci_as null,
[Dpcode3] [char] (3) Collate chinese_prc_ci_as null,
[Dpname1] [varchar] (30) Collate chinese_prc_ci_as null,
[Dpname2] [varchar] (30) Collate chinese_prc_ci_as null,
[Dpname3] [varchar] (30) Collate chinese_prc_ci_as null,
) On [primary]

Sample Data:

Dpcode1	Dpcode2	Dpcode3	Dpname1	Dpname2	Dpname3
01			Foreign Languages	Null	Null
01	02		Foreign Languages	Japanese major	Null
01	02	001	Foreign Languages	Japanese major	Class 0331
01	03		Foreign Languages	Business English	Null
01	03	001	Foreign Languages	Business English	Class 0341
01	04		Foreign Languages	Instructor	Null
01	03	002	Foreign Languages	Business English	Class 0242