Show a unary relationship hierarchy in one field in MS SQL

Question

I have a table called "Services" which contains a ServiceID, a ParentID and a Description, where the ParentID is the ServiceID of another record. The data is setup in such a way that it forms a multiple level hierarchy of items and the "Root" items have the ParentID set to zero. How can I have a query with a new field that shows a thread of all the parents up to the root parent for each record. Of course, root items will have this field as blank. Using cars as an example, I would like to have such text inside this field for the entry 'X3' and 'Punto' respectively:

Automobiles > Germany > BMW > 4 Wheel Drive > X3

Automobiles > Italy > FIAT > Front Wheel Drive > Punto

I suspect I should have a function to which I feed the ServiceID and which does the necessary recursion to get me the string value containing the threaded descriptions. Tried Googling unary relations but could not find an example with the code for the function I need.

Thanks in advance!

UPDATE:

Here is what my table looks like:

CREATE TABLE [dbo].[Services](
[ServiceID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NULL,
[ServiceDescription] [nvarchar](250) NULL)

Answer 1

Here is a generic example. You can copy, paste and run it and it will show you the output. If this works you should be able to modify the table and column values to get it to work for your situation.

If      Object_ID('dbo.Services') Is Not Null Drop Table [dbo].[Services];
Create  Table [dbo].[Services] (ServiceID Int Identity(1,1) NOT NULL, ParenServiceID Int NULL, ServiceDescription Nvarchar(250) NULL);

Insert  [dbo].[Services]
Select  null, 'Automobiles'
Union   All
Select  1, 'Germany'
Union   All
Select  2, 'BMW'
Union   All
Select  3, '4 Wheel Drive'
Union   All
Select  1, 'Italy'
Union   All
Select  5, 'FIAT'
Union   All
Select  4, 'X3'
Union   All
Select  6, 'Front Wheel Drive'
Union   All
Select  8, 'Punto';

With    recurCTE As
(
        Select  h.ServiceID, h2.ParenServiceID As nextParent, Convert(Varchar(max),Isnull(h2.ServiceDescription + ' > ','') + h.ServiceDescription) As Hierarchy
        From    [dbo].[Services] h
        Left    Join [dbo].[Services] h2
                On  h.ParenServiceID = h2.ServiceID
        Union   All
        Select  rc.ServiceID, h.ParenServiceID As nextParent, Convert(Varchar(max),Isnull(h.ServiceDescription + ' > ','') + rc.Hierarchy) As Hierarchy
        From    recurCTE rc
        Join    [dbo].[Services] h
                On  rc.nextParent = h.ServiceID
),      orderedResults As
(
        Select  ServiceID, Hierarchy
        From   (Select  Row_Number() Over (Partition By ServiceID Order By Len(Hierarchy) Desc) As lenPriID,
                        ServiceID,
                        Hierarchy
                From    recurCTE) As n
        Where   lenPriID = 1
)
Select  h.*, o.Hierarchy
From    orderedResults o
Join    [dbo].[Services] h
        On  o.ServiceID = h.ServiceID
Where   ServiceDescription In ('X3','Punto');