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