Using A Cte To Split Results Across A Cross Apply

I have some data that I need to output as rows containing markup tags, which I'm doing inside a table valued function. This has been working fine up to a point using code in the fo

Solution 1:

May be you are looking for something like this (it' a simplified version, I used only first name and comment_date as "identifier"). I tested it using this data and - for the moment - imaging max len 50 to split text column. Tip: change comment_text datatype to VARCHAR(500)

DECLARE@CommentsTABLE
(
    id INTNOTNULLIDENTITYPRIMARY KEY CLUSTERED,
    comment_date DATETIME NOTNULL,
    first_name VARCHAR(50) NOTNULL,
    last_name VARCHAR(50) NOTNULL,
    comment_title VARCHAR(50) NOTNULL,
    comment_text VARCHAR(500)
);

    INSERTINTO@CommentsVALUES(CURRENT_TIMESTAMP, 'Bob', 'Example','Bob''s Comment', 'Text of Bob''s comment.');
    INSERTINTO@CommentsVALUES(CURRENT_TIMESTAMP, 'Alice', 'Example','Alice''s Comment'
    , 'Text of Alice''s comment that is much longer and will need to be split over multiple rows aaaaaa bbbbbb cccccc ddddddddddd eeeeeeeeeeee fffffffffffff ggggggggggggg.');

WITH CTE AS (SELECT comment_date, first_name, '<Note>'+CAST( SUBSTRING(comment_text, 1, 50) ASVARCHAR(500)) +'</Note>'comment_text, 1AS RN
             FROM@CommentsUNIONALLSELECT A.comment_date, A.first_name, '<Text>'+CAST( SUBSTRING(A.comment_text, B.RN*50+1, 50) ASVARCHAR(500)) +'</Text>'AS comment_text, B.RN+1AS RN
             FROM@Comments A 
             INNERJOIN CTE B ON A.comment_date=B.comment_date AND A.first_name=B.first_name 
            WHERE  LEN(A.comment_text) > B.RN*50+1                    
             )
SELECT A.comment_date, A.first_name, '<title>'+ comment_title+'</title>'AS markup  
FROM@Comments A
UNIONALLSELECT B.comment_date, B.first_name, B.comment_text AS markup  
FROM  CTE B ;

Output:

    comment_date        first_name  markup
2017-07-07 14:30:51.117 Bob         <title>Bob's Comment</title>
2017-07-07 14:30:51.117 Alice       <title>Alice's Comment</title>
2017-07-07 14:30:51.117 Bob          <Note>Text of Bob's comment.</Note>
2017-07-07 14:30:51.117 Alice        <Note>Text of Alice's comment that is much longer and wi</Note>
2017-07-07 14:30:51.117 Alice        <Text>ll need to be split over multiple rows aaaaaa bbbb</Text>
2017-07-07 14:30:51.117 Alice        <Text>bb cccccc ddddddddddd eeeeeeeeeeee fffffffffffff g</Text>
2017-07-07 14:30:51.117 Alice        <Text>gggggggggggg.</Text>

Solution 2:

Here's a solution that also allows sorting the resultset

It uses a recursive CTE to calculate the positions in the long text. And by joining the table to the CTE, the text can be sliced up into rows.

withcteas 
(
  select id, 1 as lvl, len(comment_text) as posmax, 1 pos1, 50 limit
  from @Comments
  union all
  select id, lvl + 1, posmax, iif(pos1+limit<posmax,pos1+limit,posmax), limit
  from cte
  where pos1+limit<posmax
)
, CTE2AS 
(
select id, 0 as lvl,
 comment_date,
 concat(first_name,' ',last_name) as commenter,
 '<Title>'+rtrim(comment_title)+'</Title>' as markup
from @Comments
union all
select t.id, c.lvl,
 comment_date,
 concat(first_name,' ',last_name) as commenter_name,
 concat(iif(lvl=1,'<Note>','<Text>'),substring(comment_text,pos1,limit),iif(lvl=1,'</Note>','</Text>')) as markup
from @Comments t 
join cte c on c.id = t.id
)
selectcomment_date, commenter, markupfromCTE2orderbyid, lvl;

Output:

comment_date             commenter     markup
-----------------------  ------------  -------------------------------------
2017-07-07 15:06:31.293  Bob Example   <Title>Bob's Comment</Title>
2017-07-07 15:06:31.293  Bob Example   <Note>Text of Bob's comment.</Note>
2017-07-07 15:06:31.293  Alice Example <Title>Alice's Comment</Title>
2017-07-07 15:06:31.293  Alice Example <Note>Text of Alice's comment that is much longer and wi</Note>
2017-07-07 15:06:31.293  Alice Example <Text>ll need to be split over multiple rows.</Text>