Sql: divide los valores de las filas recurrentes en nuevas columnas

Tengo una consulta que enumera las ciudades del mismo nombre que se encuentran en diferentes estados

+----------+-------+
| city     | state |
+----------+-------+
| x        | melb  | 
| x        | syd   | 
| y        | bris  | 
| y        | ACT   | 
+----------+-------+

Quiero formatear como este

+----------+-------+--------+--------+
| city1    | state1|city2   |state2  |
+----------+-------+--------+--------+
| x        | melb  | x      | syd    |
| y        | bris  | y      | ACT    |
+----------+-------+--------+--------+

Etiqueta tu pregunta con la base de datos que estás utilizando.

- Gordon Linoff

27/03/2021 a las 13:03

------------------------------------

Si quieres pares, utiliza una unión automática:

select c1.city, c1.state, c2.state
from cities c1 join
     cities c2
     on c1.city = c2.city and c1.state < c2.state;

Pero eso es insatisfactorio. Prefiero ver todo el estados para una ciudad determinada en una fila. Para esto, se puede utilizar la agregación de cadenas; las funciones específicas difieren según la base de datos. Pero el estándar SQL especifica listagg():

select city, listagg(state, ', ') within group (order by state)
from cities
group by city;

2

entonces, para crear la tabla, uno otras 2 tablas para tener todos los datos relevantes, ¿puedo unirme yo mismo a una tabla que ha sido creada mediante una combinación interna en la misma consulta?

- Shitathakin

28 de marzo de 2021 a las 2:38

@shitathakin. . . Simplemente use un CTE. Sin embargo, observo que hiciste otra pregunta, donde muestro cómo hacerlo.

- Gordon Linoff

28/03/2021 a las 12:11

------------------------------------

Sin conocer su dbms es difícil sugerir una solución mejor. Si solo tienes dos estados para una ciudad, compruébalo:

Esquema y sentencias de inserción:

 create table mytable (city     varchar(10), state varchar(50));
 insert into mytable values ('x','melb');
 insert into mytable values ('x','syd');
 insert into mytable values ('y','bris');
 insert into mytable values ('y','ACT');  

Consulta:

 select city city1,max(state) State1,city city2,min(state)state2 from mytable
 group by city

Salida:

ciudad1 Estado1 ciudad2 estado2 X syd X melb y bris y ACTO

db<>juega aquí

------------------------------------

Creo que podríamos usar una consulta interna para separar la tabla original en 2 tablas: (ciudad1, estado 1) y (ciudad2, estado 2), con la condición de ciudad 1 = ciudad 2, estado 1 <> estado 2. Y luego únelos

Compartir mejorar esta respuesta Seguir Respondido

27 de marzo de 2021 a las 4:00

Hoainguyén

Hoainguyén

1

1

Hola @Hoainguyen. No utilice la función Respuestas para dar opiniones sobre cosas que podrían funcionar. El propósito de la función Respuesta es brindar instrucciones específicas y claras que acercarán al autor a una solución.

- Dennis

27/03/2021 a las 20:38