# Thread: Sort Table by forming a Chain from Column A to Column B

1. Registered User
Join Date
Sep 2009
Posts
90

## Unanswered: Sort Table by forming a Chain from Column A to Column B

I have table with the following structure and sample data:

 ColumnA Column B James Peter Paul Nathan Nick Ryan Peter Paul John Adam Ryan Daniel

The result should be sorted in such a way that it was chained from Column A to Column B like the table below:

 ColumnA Column B James Peter Peter Paul Paul Nathan John Adam Nick Ryan Ryan Daniel

If ColumnB is existing in Column A then it should be next in the next row and so on.
Is it doable in Microsoft SQL Server? or should I use another language like C#?

I hope you can help me. Thanks in Advance!

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
You're going to want to take a look at recursive CTEs...

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I've run out of lunch break, but here's a starter for you:
Code:
```DECLARE @x table (
name1 nchar(10)
, name2 nchar(10)
, id    int IDENTITY(-93, 7)
);

INSERT INTO @x (name1, name2)
VALUES (N'James', N'Peter')
, (N'Paul' , N'Nathan')
, (N'Nick' , N'Ryan')
, (N'Peter', N'Paul')
, (N'Ryan' , N'Daniel')
;

; WITH recursive_bit AS (
SELECT name1 AS anchor
, name1
, name2
, 0 AS leveler
FROM   @x

UNION ALL

SELECT recursive_bit.anchor
, x.name1
, x.name2
, recursive_bit.leveler + 1
FROM   @x AS x
INNER
JOIN recursive_bit
ON recursive_bit.name2 = x.name1

)
SELECT *
FROM   recursive_bit
ORDER
BY recursive_bit.anchor
, recursive_bit.leveler
;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•