USE [Test]
GO
/****** Object:  Table [dbo].[Products]    Script Date: 2013-02-28 13:58:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
	[NodeId] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeId] [int] NULL,
	[Text] [nvarchar](255) NULL,
	[Active] [bit] NULL,
	[CustID] [int] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[NodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (1, NULL, N'Blues', 1, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (3, NULL, N'Pop', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (4, NULL, N'Rock', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (5, 4, N'Elvis Presley', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (6, 3, N'Duran Duran', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (7, 4, N'Moody Blue', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (8, 4, N'Aloha from Hawaii Via Satellite', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (9, 3, N'Notorious ', NULL, 20)
GO
INSERT [dbo].[Products] ([NodeId], [ParentNodeId], [Text], [Active], [CustID]) VALUES (10, 3, N'Decade: Greatest Hits ', NULL, 20)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO

;WITH cte AS
(
SELECT
	p.NodeId,
	p.ParentNodeId,
	p.Text,
	p.Active,
	p.CustID,
	CAST([Text] AS VARCHAR(256)) AS CombinedName,
	CAST([Text] AS VARCHAR(256)) AS ArticleName
FROM
	Products p
WHERE
	ParentNodeId IS NULL
UNION ALL

SELECT
	p.NodeId,
	p.ParentNodeId,
	p.Text,
	p.Active,
	p.CustID,
	CAST(c.CombinedName + '\' + p.Text AS VARCHAR(256)),
	CAST(p.Text AS VARCHAR(256))
FROM
	Products p
	INNER JOIN cte c ON c.NodeId = p.ParentNodeId
)
SELECT * FROM cte;