Thesql… compare and match items in same column

MySQL

I have a column full of server names. For example;

LouServer01
VgsServer01
LouServer02
VgsServer02

I need/want to be able to create a query that can match those two names based on everything after Lou/Vgs and give me something that looks like this:

Louisville  | Vegas
____________|____________
LouServer01 | VgsServer01
LouServer02 | VgsServer02

Best Answer

If the server naming format/convention is fixed then you can get desired output using conditional aggregation

SELECT 
       MAX(CASE WHEN LEFT(name, 3) = 'Lou' THEN name END) Louisville,
       MAX(CASE WHEN LEFT(name, 3) = 'Vgs' THEN name END) Vegas
  FROM servers
 GROUP BY RIGHT(name, 2)

Output:

|  LOUISVILLE |       VEGAS |
|-------------|-------------|
| LouServer01 | VgsServer01 |
| LouServer02 | VgsServer02 |

Here is SQLFiddle demo