Using Row Values as Columns?

Question

I'm just starting to learn mySql and am having trouble getting the table that I want.

I have a table that looks like this:

name | state | city    
|----------------------------|
bob  | fl    | miami 
bob  | ca    | san francisco

and I'm trying to make a table that takes the cities of Bob and turn it into two columns so it would look like this:

name     |  FL city    |  CA city
|--------------------------------------|    
bob      |     miami   |  san francisco

how would I be able to do this? There are name ids from previous queries that I could use so bob for example is 1. (also this is my first time posting on stack so apologies for the terrible formatting) thanks in advance :)

Seriously consider handling issues of data display in application code. — Strawberry, Feb 28 '21 at 09:58

score 0 · Answer 1 · answered Feb 27 '21 at 23:24

0

one way if the number of states are limited :

select 
 name 
, max(case when state = 'FL' then city end) as "FL city"
, max(case when state = 'CA' then city end) as "CA city"
group by name

or using pivot otherwise you need to use dynamic sql

answered Feb 27 '21 at 23:24

eshirvana

23,227
3
22
38

score 0 · Answer 2 · answered Feb 27 '21 at 23:35

it is called pivot, to trans dforms rows into columns

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

db<>fiddle here

Using Row Values as Columns?

2 Answers2