如何实现SQL同日期的name字段拆分多列，避免日期重复？

Hey there! To get the output format you want—where each date appears once followed by all associated names separated by " - "—you'll need to use string aggregation functions specific to your database. Here are examples for the most common systems:

MySQL / MariaDB #

Use GROUP_CONCAT to aggregate names, then combine with the date using CONCAT_WS:

SELECT 
  CONCAT_WS(' - ', day, GROUP_CONCAT(name SEPARATOR ' - ')) AS formatted_result
FROM table1
GROUP BY day;

If you want names sorted in a specific order, add ORDER BY inside GROUP_CONCAT:

GROUP_CONCAT(name ORDER BY name SEPARATOR ' - ')

PostgreSQL #

Use STRING_AGG for aggregation, and cast the date to text to match string types:

SELECT 
  CONCAT_WS(' - ', day::TEXT, STRING_AGG(name, ' - ' ORDER BY name)) AS formatted_result
FROM table1
GROUP BY day;

SQL Server (2017+) #

Leverage STRING_AGG and cast the date to a string:

SELECT 
  CONCAT_WS(' - ', CAST(day AS VARCHAR(10)), STRING_AGG(name, ' - ') WITHIN GROUP (ORDER BY name)) AS formatted_result
FROM table1
GROUP BY day;

Oracle #

Use LISTAGG to concatenate names, then append to the date:

SELECT 
  day || ' - ' || LISTAGG(name, ' - ') WITHIN GROUP (ORDER BY name) AS formatted_result
FROM table1
GROUP BY day;

Each query groups rows by the day column, aggregates all associated name values into a single string, then combines the date with the aggregated names using " - " as the separator.

内容的提问来源于stack exchange，提问作者nico