从1列写2列

Question

I have a table

Registration
╔══════╦══════════════╦══════════════╦═══════════════╗  
║name1 ║   name2      ║ name2's state║   entity_id   ║
╠══════╬══════════════╬══════════════╣═══════════════╣
║  1   ║     92       ║   complete   ║     152       ║
║  92  ║     1        ║   pending    ║     153       ║
╚══════╩══════════════╩══════════════╩═══════════════╝

Is it possible to replace a row's name2 state into "name1's state"? I mean A state 1-->92 is "complete" I know that state 92--->1 is "pending" the question is how to display the same information in the table in one row like in my_table? Is it possible?

my_table
╔══════╦══════════════╦═══════════════╦═══════════════╦═══════════════╗  
║name1 ║     name2    ║ name2's state ║ name1's state ║   entity_id   ║
╠══════╬══════════════╬═══════════════╣═══════════════╣═══════════════╣
║  1   ║     92       ║   complete    ║  pending      ║     152       ║
║  92  ║     1        ║   pending     ║  complete     ║     153       ║
╚══════╩══════════════╩═══════════════╩═══════════════╩═══════════════╝

Answer 1

What you will want to do is something like this

SELECT
 t1.name1,
 t1.name2,
 t1.name2state as name1state,
 t2.name2state as name2state,
 t1.entity_id
FROM
 names t1
JOIN
 names t2
ON
 t1.name2 = t2.name1;
ORDER BY
 t1.name1;

here is an SQL fiddle you can play with.

http://sqlfiddle.com/#!2/79222/4

of course this just gives you the data you asked for and an example of a join, you will need to appy this and probably change it in a way to suit your needs.

Edit:

JOIN keyword for a standard join, this JOIN clause will only show records where the ON statement is true. The ON clause is denoting which two columns are being compared for the JOIN. so in the above SQL statement you are saying you want to SELECT your data from TABLE 1 that has TABLE 2 JOINED onto TABLE 1 where TABLE1's column name2 = TABLE2's column name1. There are many other types of JOINs you can use to manipulate that data. Look at this Stackoverflow thread to see all of the types of JOINs you can use and what they do.

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?