title: Pivoting rows to columns with custom column names in SQL Server
body: <p>I'm having some difficulty with pivoting rows into columns as I also want to name the columns. Here is my current code, modified:</p>
  <pre><code>SELECT Message, value
  FROM Table1
  CROSS APPLY 
  (
  SELECT value FROM STRING_SPLIT(Message,'&quot;')
  WHERE value LIKE '%.%'
  )
  AS SourceTable
  </code></pre>
  <p>And my current output:</p>
  <pre><code>Message      value 
  ------------ -----
  longmessage1 hello
  longmessage1 hi
  longmessage1 hey
  longmessage1 hola
  </code></pre>
  <p>Just for the sake of shortness, I replaced the actual Message with longmessage1 above. My desired output:</p>
  <pre><code>Message      greeting1 greeting2 greeting3 greeting4
  ------------ --------- --------- --------- ---------
  longmessage1     hello        hi       hey      hola
  </code></pre>
  <p><strong>The maximum amount of greetings is six, and if a Message doesn't have six, I'm fine with the value of, say greeting 4 and 5 to be NULL.</strong></p>
  <p>FYI- I am using SQL Server. I think I could somehow use PIVOT to do this but I'm stuck on the custom column name part and if CROSS APPLY was even the right idea. If anyone could offer some suggestions, that'd be terrific. Thank you!</p>