这里，我的意图是在不使用group_concat（）函数的情况下应用字符串连接：

Set @concatHobbies = '';
SELECT TRIM(LEADING ', ' FROM T.hobbies ) FROM 
(
   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies
)T
Order by Id DESC
LIMIT 1

Here

   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies

将返回

  Id    hobbies
  1     , shopping
  2     , shopping, fishing
  3     , shopping, fishing, coding

现在我们的预期结果是第三。所以我用

  Order by Id DESC 
  LIMIT 1
  

然后我也将第一个“，”从字符串中删除

 TRIM(LEADING ', ' FROM T.hobbies )

试试看：

DECLARE @Hobbies NVARCHAR(200) = ' '

SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;

TL；博士；

set @sql='';
set @result='';
set @separator=' union \r\n';
SELECT 
@sql:=concat('select ''',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME  ,''' as col_name,',
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH ,' as def_len ,' ,
'MAX(CHAR_LENGTH(',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , '))as  max_char_len',
' FROM ',
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
) as sql_piece, if(@result:=if(@result='',@sql,concat(@result,@separator,@sql)),'','') as dummy
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE like '%char%'
and INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='xxx' 
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='yyy';
select @result;

现在已经很晚了，但对于那些正在搜索“使用透视表将多个MySQL行连接到一个字段中”的人来说会很有帮助：）

查询：

SELECT pm.id, pm.name, GROUP_CONCAT(c.name) as channel_names
FROM payment_methods pm
LEFT JOIN payment_methods_channels_pivot pmcp ON pmcp.payment_method_id = pm.id
LEFT JOIN channels c ON c.id = pmcp.channel_id
GROUP BY pm.id

桌子

payment_methods 
  id  | name
  1   | PayPal

channels
  id  | name
  1   | Google
  2   | Faceook

payment_methods_channels_pivot
   payment_method_id | channel_id
   1                 |  1
   1                 |  2

输出：

本例中另一个有趣的例子-

下面是people_hobbies示例表的结构-

DESCRIBE people_hobbies;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| ppl_id  | int unsigned | YES  | MUL | NULL    |                |
| name    | varchar(200) | YES  |     | NULL    |                |
| hby_id  | int unsigned | YES  | MUL | NULL    |                |
| hobbies | varchar(50)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

该表的填充方式如下-

SELECT * FROM people_hobbies;
+----+--------+-----------------+--------+-----------+
| id | ppl_id | name            | hby_id | hobbies   |
+----+--------+-----------------+--------+-----------+
|  1 |      1 | Shriya Jain     |      1 | reading   |
|  2 |      4 | Shirley Setia   |      4 | coding    |
|  3 |      2 | Varsha Tripathi |      7 | gardening |
|  4 |      3 | Diya Ghosh      |      2 | fishing   |
|  5 |      4 | Shirley Setia   |      3 | gaming    |
|  6 |      1 | Shriya Jain     |      6 | cycling   |
|  7 |      2 | Varsha Tripathi |      1 | reading   |
|  8 |      3 | Diya Ghosh      |      5 | shopping  |
|  9 |      3 | Diya Ghosh      |      4 | coding    |
| 10 |      4 | Shirley Setia   |      1 | reading   |
| 11 |      1 | Shriya Jain     |      4 | coding    |
| 12 |      1 | Shriya Jain     |      3 | gaming    |
| 13 |      4 | Shirley Setia   |      2 | fishing   |
| 14 |      4 | Shirley Setia   |      7 | gardening |
| 15 |      2 | Varsha Tripathi |      3 | gaming    |
| 16 |      2 | Varsha Tripathi |      2 | fishing   |
| 17 |      1 | Shriya Jain     |      5 | shopping  |
| 18 |      1 | Shriya Jain     |      7 | gardening |
| 19 |      3 | Diya Ghosh      |      1 | reading   |
| 20 |      4 | Shirley Setia   |      5 | shopping  |
+----+--------+-----------------+--------+-----------+

现在，生成了一个表hobby_list，其中包含所有人的列表和每个人的爱好列表，每个爱好在一行中-

CREATE TABLE hobby_list AS
    -> SELECT ppl_id, name,
    -> GROUP_CONCAT(hobbies ORDER BY hby_id SEPARATOR "\n")
    -> AS hobbies
    -> FROM people_hobbies
    -> GROUP BY ppl_id
    -> ORDER BY ppl_id;

SELECT * FROM hobby_list;

在我的例子中，我有一行ID，必须将其转换为char，否则，结果被编码为二进制格式：

SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table

在sql server中，使用string_agg将行字段值透视为列：

select string_agg(field1, ', ') a FROM mytable 

or

select string_agg(field1, ', ') within group (order by field1 dsc) a FROM mytable group by field2