• Many thanks Eirikur

    I am tending towards this solution  (for reason not explained in my post)

    -- CASE METHOD
    DECLARE @NUM_LEAD INT = 3;
    SELECT
    TD.id
    ,TD.detdate
    ,TD.list
    FROM
    (
    SELECT id,
     detdate,
     detail
     + CASE WHEN @NUM_LEAD > 1 THEN '-' + LEAD(detail, 1) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
     + CASE WHEN @NUM_LEAD > 2 THEN '-' + LEAD(detail, 2) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
     + CASE WHEN @NUM_LEAD > 3 THEN '-' + LEAD(detail, 3) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
     + CASE WHEN @NUM_LEAD > 4 THEN '-' + LEAD(detail, 4) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
      AS list
    FROM dbo.testdata02
    ) TD
    WHERE list IS NOT NULL;

    can this converted into a table function that can be use with CROSS APPLY?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day