score:3

Accepted answer

try this:

declare @YourTable table (RowID int primary key, NextID int, TextValue varchar(50))

INSERT INTO @YourTable VALUES (1 , 12  ,'The quick')
INSERT INTO @YourTable VALUES (3 , 40  ,'jumps over')
INSERT INTO @YourTable VALUES (5 , null,'lazy dog.')
INSERT INTO @YourTable VALUES (12, 3   ,'brown fox')
INSERT INTO @YourTable VALUES (40, 5   ,'the')

;with cteview as (
SELECT * FROM @YourTable WHERE RowID=1
UNION ALL
SELECT y.* FROM @YourTable y
    INNER JOIN cteview   c ON y.RowID=c.NextID
) 
select * from cteview
OPTION (MAXRECURSION 9999) --go beyond default 100 levels of recursion to 9999 levels

OUTPUT:

RowID       NextID      TextValue
----------- ----------- --------------------------------------------------
1           12          The quick
12          3           brown fox
3           40          jumps over
40          5           the
5           NULL        lazy dog.

(5 row(s) affected)

score:0

LINQ answer:

table.OrderBy(sentence => sentence.NextID);

Edit: I hope I answered it correctly this time:

class Sentence
{
    public int Id;
    public int? NextId;
    public string Text;
    public Sentence(int id, int? nextId, string text)
    {
        this.Id = id;
        this.NextId = nextId;
        this.Text = text;
    }
}

var Sentences = new [] {
    new Sentence(1, 12, "This quick"),
    new Sentence(3, 40, "jumps over"),
    new Sentence(5, null, "lazy dog."),
    new Sentence(12, 3, "brown fox"),
    new Sentence(40, 5, "the"),
};

Func<int?, string> GenerateSentence = null;
GenerateSentence = (id) => id.HasValue? Sentences
    .Where(s => s.Id == id.Value)
    .Select(s => s.Text + " " + GenerateSentence(s.NextId))
    .Single() : string.Empty;

Console.WriteLine(GenerateSentence(1));

score:0

If you are using LINQ to SQL/Entities, then the generated Sentence class should have all those properties you mentioned, as well as an entity reference to the next sentence (let's call it NextSentence) from the foreign key.

Then you can just do:

Sentence s = Sentences.First();
StringBuilder sb = new StringBuilder();
do { sb.Append(s.Text); s = s.NextSentence; } while (s != null);

and sb.ToString() will have your answer.


Related Articles