Search code examples
mysqlsql-serverpostgresqlstring-aggregation

Combining strings by different type in SQL PSQL or SQL server

How to combine strings by different type in Mysql, PostgreSQL or SQL Server, below data type = 1 is province and type = 2 is city, I would like to combine them to a column,

name         code       type
Sichuan     610000       1    
Chengdu     610000       2
Hubei       430000       1
Wuhan       430000       2

and my expected output is below:

name              code
SichuanChengdu    610000
HubeiWuhan        430000
Solution

  • In Postgres you can use:

    select string_agg(name, '' order by type) code
from the_table
group by code;