Separate different types of values from the same column into separate columns

Transact-SQL
1

I have one column that contains different ways of calling each USER (email, phone), there is a separate line for each way of calling. I want to consolidate the information, which will be presented to me in the same line mail, phone of the USER. How can I write this in a query?
for example:
Untitled-1

Thanks

2

Query below. I wasn't able to test it since you didn't provide any usable test data.


SELECT
    USER,
    MAX(CASE WHEN row_num =  1 THEN COMMUNICATION END) AS EMAIL1,
    MAX(CASE WHEN row_num =  2 THEN COMMUNICATION END) AS EMAIL2,
    MAX(CASE WHEN row_num = 11 THEN COMMUNICATION END) AS PHONE1,
    MAX(CASE WHEN row_num = 12 THEN COMMUNICATION END) AS PHONE2
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY USER, 
        CASE WHEN COMMUNICATION LIKE '%@%' THEN 0 ELSE 1 END ORDER BY ID) +
        CASE WHEN COMMUNICATION LIKE '%@%' THEN 0 ELSE 10 END AS row_num
    FROM dbo.table_name
) AS derived
GROUP BY USER
ORDER BY USER
1 Like