如何在SQL中将列数据拼接为单个字段？含查询改写需求

Hey Mike! Let's break down your two SQL needs step by step.

This is a super common task, but the exact method depends on which database you're using. Here are the go-to functions for major SQL dialects:

• MySQL/MariaDB: Use GROUP_CONCAT() to aggregate values from multiple rows into one string.
  Example: If you want to combine all user emails from a users table with a comma separator:

  SELECT GROUP_CONCAT(email SEPARATOR ', ') AS concatenated_emails
  FROM users;
  

• SQL Server (2017+) / PostgreSQL: Use STRING_AGG() for row aggregation.
  Example (works for both):

  SELECT STRING_AGG(email, ', ') AS concatenated_emails
  FROM users;
  

• Oracle: Use LISTAGG() with an order clause to control the concatenation sequence:

  SELECT LISTAGG(email, ', ') WITHIN GROUP (ORDER BY email) AS concatenated_emails
  FROM users;
  

If you're combining multiple columns from the same row (not aggregating rows), just use string concatenation functions or operators:

-- Universal approach with CONCAT() (works across most databases)
SELECT CONCAT(col1, ' | ', col2, ' | ', col3) AS combined_columns
FROM your_table;

Based on your expected output, you want a single row that includes the table name and a merged string of four specific column names. Here are two scenarios:

Scenario 1: Fixed, hardcoded result #

If you just need to output the exact string you specified, a simple SELECT will do:

SELECT 
  'TableName: 20180301_Vitality' AS table_info,
  '_CustomObjectKey | Email address | Subscriber key | opCoCode' AS column_data;

Scenario 2: Dynamic retrieval from system tables #

If you want to pull the column names dynamically from your database's metadata (so it updates if columns change), use the appropriate system table for your database. For example, in SQL Server/MySQL:

-- SQL Server/MySQL example
SELECT 
  CONCAT('TableName: ', TABLE_NAME) AS table_info,
  STRING_AGG(COLUMN_NAME, ' | ') AS column_data
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '20180301_Vitality'
  AND COLUMN_NAME IN ('_CustomObjectKey', 'Email address', 'Subscriber key', 'opCoCode')
GROUP BY TABLE_NAME;

This query will fetch the four columns from the metadata table, concatenate them with | separators, and return a single row with the table name and merged column string—matching your desired output exactly.

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