CREATE TABLE [dbo]. [T] (
[No] [nvarchar] (50) NOT NULL,
[Name] [nvarchar] (50) NOT NULL,
[ParentNo] [nvarchar] (50) NOT NULL
) ON the (PRIMARY)
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (' 0 'N, N' guangdong province ', N ')
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (N '100', N 'shenzhen', N '0')
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (N '100 a' N 'nanshan district', N '100')
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (N '110 a' N 'baoan district', N '100')
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (N '200', N 'guangzhou', N '0')
GO
INSERT [dbo]. [T] ([No], [Name], [ParentNo]) VALUES (N '200 a' N 'yuexiu district', N '200')
GO
The query results
CodePudding user response:
The CREATE TABLE # T (
[No] [nvarchar] (50) NOT NULL,
[Name] [nvarchar] (50) NOT NULL,
[ParentNo] [nvarchar] (50) NOT NULL
)
INSERT # T ([No], [Name], [ParentNo]) VALUES (' 0 'N, N' guangdong province ', N ')
INSERT # T ([No], [Name], [ParentNo]) VALUES (N '100', N 'shenzhen', N '0')
INSERT # T ([No], [Name], [ParentNo]) VALUES (N '100 a' N 'nanshan district', N '100')
INSERT # T ([No], [Name], [ParentNo]) VALUES (N '110 a' N 'baoan district', N '100')
INSERT # T ([No], [Name], [ParentNo]) VALUES (N '200', N 'guangzhou', N '0')
INSERT # T ([No], [Name], [ParentNo]) VALUES (N '200 a' N 'yuexiu district', N '200')
; WITH ct
AS
(
SELECT No, Name, ParentNo, 0 AS lv, CONVERT (VARCHAR (Max), RIGHT (' 0000 '+ CONVERT (VARCHAR (10), ROW_NUMBER () OVER (ORDER BY No)), 4)) AS the FROM p # T WHERE ParentNo='
UNION ALL
The SELECT b.N o, b.N ame, p. arentNo, lv + 1, p + RIGHT (' 0000 '+ CONVERT (VARCHAR (10), ROW_NUMBER () OVER (ORDER BY b.N o)), (4) the FROM ct a INNER JOIN # T b ON a.N o=b. ParentNo
)
The SELECT a.N o, a.N ame, Amy polumbo arentNo, (SELECT ', '+ no FROM ct WHERE p LIKE Amy polumbo +' % 'FOR XML PATH ('')) AS allchildno, lv,
CASE
The WHEN the name LIKE '% % province' THEN 'province'
WHEN the name LIKE '% %,' THEN ', '
The WHEN the name LIKE '% area %' THEN 'area'
The WHEN the name LIKE '% % county,' THEN 'county'
WHEN the name LIKE '% % town' THEN ', '
The ELSE '
END AS the category of the FROM ct a
The ORDER BY p
DROP TABLE # T