Pivoting rows to columns with custom column names in SQL Server

I’m having some difficulty with pivoting rows into columns as I also want to name the columns. Here is my current code, modified:

SELECT Message, value
FROM Table1
CROSS APPLY 
(
SELECT value FROM STRING_SPLIT(Message,'"')
WHERE value LIKE '%.%'
)
AS SourceTable

And my current output:

Message      value 
------------ -----
longmessage1 hello
longmessage1 hi
longmessage1 hey
longmessage1 hola

Just for the sake of shortness, I replaced the actual Message with longmessage1 above. My desired output:

Message      greeting1 greeting2 greeting3 greeting4
------------ --------- --------- --------- ---------
longmessage1     hello        hi       hey      hola

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.

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!

Answer

You can use row_number() and conditional aggregation:

SELECT t1.Message, a.*
FROM Table1 t1 CROSS APPLY 
     (SELECT MAX(CASE WHEN seqnum = 1 THEN value END) as greeting1,
             MAX(CASE WHEN seqnum = 2 THEN value END) as greeting2,
             MAX(CASE WHEN seqnum = 3 THEN value END) as greeting3,
             MAX(CASE WHEN seqnum = 4 THEN value END) as greeting4,
             MAX(CASE WHEN seqnum = 5 THEN value END) as greeting5,
             MAX(CASE WHEN seqnum = 6 THEN value END) as greeting6
      FROM (SELECT s.value,
                   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as seqnum
            FROM STRING_SPLIT(t1.Message,'"')
            WHERE value LIKE '%.%'
           ) s
     ) s;

Note: In practice, this will probably preserve the ordering of the values. However that is not guaranteed — based on the documentation.