Try the below code...

DECLARE @TransactionComponents TABLE

(

pkTransactionComponent int IDENTITY(1,1) NOT NULL,

pkTransactionID int NOT NULL,

ComponentID int NULL

)

INSERT INTO @TransactionComponents(pkTransactionID, ComponentID)

SELECT 1,5

UNION SELECT 1,6

UNION SELECT 1,7

UNION SELECT 1,8

UNION SELECT 1,9

UNION SELECT 1,10

UNION SELECT 10,2

UNION SELECT 2,11

UNION SELECT 2,12

UNION SELECT 3,4

DECLARE @Id INT

SELECT @Id = 1

;WITH Hierarchy AS

(

SELECT * FROM @TransactionComponents

WHERE pktransactionid = @Id

UNION ALL

SELECT A.* FROM @TransactionComponents AS A

INNER JOIN Hierarchy AS B ON A.pktransactionid = B.componentid

)

SELECT * FROM Hierarchy