title: Using a CTE to split results across a CROSS APPLY
body: <p>I have some data that I need to output as rows containing markup tags, which I'm doing inside a table valued function.</p>
  
  <p>This has been working fine up to a point using code in the format below, using the <code>search</code> query to gather my data, and then inserting into my returned table using the output from <code>results</code>.</p>
  
  <p>I now need to take a longer data field and split it up over a number of rows, and I'm at something of a loss as to how to achieve this.</p>
  
  <p>I started with the idea that I wanted to use a CTE to process the data from my query, but I can't see a way to get the data from my <code>search</code> query into my CTE and from there into my <code>results</code> set.</p>
  
  <p>I guess I can see an alternative way of doing this by creating another table valued function in the database that returns a results set if I feed it my <code>comment_text</code> column, but it seems like a waste to do it that way.</p>
  
  <p>Does anyone see a route through to a solution?</p>
  
  <p>Example "Real" Table: </p>
  
  <pre><code>DECLARE @Comments TABLE
  (
      id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
      comment_date DATETIME NOT NULL,
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      comment_title VARCHAR(50) NOT NULL,
      comment_text char(500)
  );
  </code></pre>
  
  <p>Add Comment Rows:</p>
  
  <pre><code>INSERT INTO @Comments VALUES(CURRENT_TIMESTAMP, 'Bob', 'Example','Bob''s Comment', 'Text of Bob''s comment.');
  INSERT INTO @Comments VALUES(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.');
  </code></pre>
  
  <p>Format of returned results table:</p>
  
  <pre><code>DECLARE @return_table TABLE
  (
     comment_date DATETIME,
     commenter_name VARCHAR(101),
     markup VARCHAR(100)
  );
  </code></pre>
  
  <p>Naive query (Can't run because the variable <code>comment_text</code> in the SplitComment CTE can't be identified. </p>
  
  <pre><code>WITH SplitComment(note,start_idx) AS
  (
     SELECT '&lt;Note&gt;'+SUBSTRING(comment_text,0,50)+'&lt;/Note&gt;', 0
     UNION ALL
     SELECT '&lt;Text&gt;'+SUBSTRING(note,start_idx,50)+'&lt;/Text&gt;', start_idx+50 FROM SplitComment WHERE (start_idx+50) &lt; LEN(note)
  )
  INSERT INTO @return_table
  SELECT results.* FROM
  (
     SELECT
     comment_date,
     CAST(first_name+' '+last_name AS VARCHAR(101)) commenter,
     comment_title,
     comment_text
     FROM @Comments
  ) AS search
  CROSS APPLY
  (
             SELECT comment_date, commenter, '&lt;title&gt;'+comment_title+'&lt;/title&gt;' markup
   UNION ALL SELECT comment_date, commenter, SplitComment
  ) AS results;
  
  SELECT * FROM @return_table;
  </code></pre>
  
  <p>Results (when the function is run without the CTE):</p>
  
  <pre><code>comment_date            commenter_name                                                                                        markup
  2017-07-07 11:53:57.240 Bob Example                                                                                           &lt;title&gt;Bob's Comment&lt;/title&gt;
  2017-07-07 11:53:57.240 Alice Example                                                                                         &lt;title&gt;Alice's Comment&lt;/title&gt;
  </code></pre>
  
  <p>Ideally, I'd like to get one additional row for Bob's comment, and two rows for Alice's comment. Something like this:</p>
  
  <pre><code>comment_date            commenter_name   markup
  2017-07-07 11:53:57.240 Bob Example      &lt;title&gt;Bob's Comment&lt;/title&gt;
  2017-07-07 11:53:57.240 Bob Example      &lt;Note&gt;Bob's Comment&lt;/Note&gt;
  2017-07-07 11:53:57.240 Alice Example    &lt;title&gt;Alice's Comment&lt;/title&gt;
  2017-07-07 11:53:57.240 Alice Example    &lt;Note&gt;Text of Alice''s comment that is much longer and w&lt;/Note&gt;
  2017-07-07 11:53:57.240 Alice Example    &lt;Text&gt;ill need to be split over multiple rows.&lt;/Text&gt;
  </code></pre>