I want to have unique values of one column as rows and the other columns unique values as columns. I'm not sure if this is the right explanation so I'll just leave a screenshot of what I want to happen. Sorry it was made in a rush.
Example:
You need to basically join the table to itself.
You can perform a query like this:
The Query
SELECT chat.chat_id, chat.chat_user AS chat_user1, chat2.chat_user AS chat_user2
FROM chat as chat, chat as chat2
WHERE chat.chat_id = chat2.chat_id
AND chat.chat_user != chat2.chat_user
GROUP BY chat_id
Which will join it to itself and check for all groups of chat_id and return the result with an extra column as you want.
SELECT AS
Importantly to change column names you can SELECT column1 AS new_column1, column2 AS new_column2
This is particularly useful if you have ambiguous column names which of course you will when joining a table to itself in essence.
WHERE / AND
Here we are first checking the chat_id is the same so all chat_ids that equal 14 for example get returned, however to stop it creating multiple rows we tell it to ignore chat_users that have already been put into chat_user2 on the first run through of the code.
GROUP BY
This stops it creating duplicate rows of data and returns one result for each chat_id
I've assumed you're table is called chat for this. If not replace chat with your table name
