MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  `name` VARCHAR(10),
  `state` VARCHAR(2),
  `city` VARCHAR(12)
);

INSERT INTO users
  (`name`, `state`, `city`)
VALUES
  ('Bob', 'FL', 'Miami'),
  ('Bob', 'CA', 'SanFrancisco'),
  ('Alice', 'CA', 'SanFrancisco');
SELECT
`name`,
MAX(IF (`state` = 'CA', `city`, NULL)) as 'CA city',
MAX(IF (`state` = 'FL', `city`, NULL)) as 'FL city'
FROM users
GROUP BY `name`


name
CA city
FL city


Bob
SanFrancisco
Miami

Alice
SanFrancisco
null


SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`state` = "', `state`,'", `city`,"")) AS "',`state`,' city"')
              ) INTO @sql
FROM users;


SET @sql = CONCAT('SELECT `name`,  ', @sql, ' 
                  FROM users 
                 GROUP BY `name`
                 ORDER BY `name`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


name
CA city
FL city


Alice
SanFrancisco


Bob
SanFrancisco
Miami