SQLServerで共通テーブル式(CTE)を利用して再帰呼び出しする

-- パンくずリストテーブル作成
CREATE TABLE Breadcrumbs(
	Id varchar(10) NOT NULL,
	Name nvarchar(200) NOT NULL,
	Url nvarchar(1000) NOT NULL,
	ParentId varchar(10),
CONSTRAINT [PK_Breadcrumbs] PRIMARY KEY CLUSTERED
(
	Id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- パンくずリストデータ作成
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0', 'GoodTechトップページ', 'http://goodtech.co.jp/', NULL)
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-01', '会社概要', 'http://goodtech.co.jp/company-profile/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-02', '提供サービス', 'http://goodtech.co.jp/services/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-03', 'お知らせ', 'http://goodtech.co.jp/notices/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0301', 'ホームページを公開しました', 'http://goodtech.co.jp/notices/greeting2/', 'page-03')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-04', 'お問い合わせ', 'http://goodtech.co.jp/contact/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-06', 'サイトマップ', 'http://goodtech.co.jp/sitemap/', 'page-0')

-- 共通テーブル式(CTE)を利用して再帰的にデータを取得する。
declare @Id varchar(10) = 'page-0';

WITH Breadcrumbs_CTE AS
(
	SELECT
		b1.*,
		CAST(b1.Id AS nvarchar(4000)) AS IdPath,
		CAST(b1.Name AS nvarchar(4000)) AS NamePath
	FROM
		Breadcrumbs b1
	WHERE
		b1.Id = @Id
	UNION ALL
	SELECT
		b2.*,
		Breadcrumbs_CTE.IdPath + ' - ' + b2.Id AS IdPath,
		Breadcrumbs_CTE.NamePath + ' ＞ ' + b2.Name AS NamePath
	FROM
		Breadcrumbs b2
		INNER JOIN Breadcrumbs_CTE
			ON
			b2.ParentId = Breadcrumbs_CTE.Id
)

SELECT
	cte.IdPath,
	cte.NamePath,
	cte.Url,
	cte.Id,
	cte.ParentId
FROM
	Breadcrumbs_CTE cte
ORDER BY
	cte.IdPath