Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Talking 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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    You're going to want to take a look at recursive CTEs...
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    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'John' , N'Adam')
         , (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
    ;
    George
    Home | Blog

Tags for this Thread

Posting Permissions

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