请大神帮忙写一个sql语句

[sanidcn]

1.原数据表1

2.想生成表2

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
GO
CREATE TABLE t1(
	id INT IDENTITY(1,1) PRIMARY KEY,
	pid INT,
	[name] NVARCHAR(20)	
)
GO
SET NOCOUNT ON
INSERT INTO t1(pid,[name]) VALUES (0,'组织结构');
INSERT INTO t1(pid,[name]) VALUES (1,'人力资源');
INSERT INTO t1(pid,[name]) VALUES (1,'财务');
INSERT INTO t1(pid,[name]) VALUES (1,'采购');
INSERT INTO t1(pid,[name]) VALUES (2,'招聘专员');
INSERT INTO t1(pid,[name]) VALUES (2,'招聘主管');
INSERT INTO t1(pid,[name]) VALUES (2,'人力经理');
INSERT INTO t1(pid,[name]) VALUES (3,'财务经理');
INSERT INTO t1(pid,[name]) VALUES (3,'出纳');
INSERT INTO t1(pid,[name]) VALUES (4,'采购经理');
INSERT INTO t1(pid,[name]) VALUES (4,'采购专员');
GO
---- 以上为测试表及测试数据 -----

--加一个标量函数，后面获取路径就非常方便了：
IF OBJECT_ID('Fun_GetPath') IS NOT NULL
	DROP FUNCTION Fun_GetPath
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION dbo.Fun_GetPath 
(
	@id INT	
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r NVARCHAR(MAX)
	;WITH cte AS (
	SELECT *,0 AS dep FROM t1 WHERE id=@id
	UNION ALL
	SELECT t1.*,dep+1 AS dep FROM t1 INNER JOIN cte ON t1.id=cte.pid
	)
	SELECT @r = REPLACE(STUFF( (SELECT '》'+[name] FROM cte ORDER BY dep DESC FOR XML PATH('')),1,1,''),'》','>')
	RETURN @r;
END
GO

SELECT *,dbo.Fun_GetPath(t1.id) AS [path] FROM t1