Mssql - Group_Concat

How to use GROUP_CONCAT in a CONCAT in MySQL

select id, group_concat(`Name` separator ',') as `ColumnName`
from
(
  select 
    id, 
    concat(`Name`, ':', group_concat(`Value` separator ',')) as `Name`
  from mytbl
  group by 
    id, 
    `Name`
) tbl
group by id;

You can see it implemented here : Sql Fiddle Demo. Exactly what you need.

Update
Splitting in two steps. First we get a table having all values(comma separated) against a unique[Name,id]. Then from obtained table we get all names and values as a single value against each unique id
See this explained here SQL Fiddle Demo (scroll down as it has two result sets)

Edit There was a mistake in reading question, I had grouped only by id. But two group_contacts are needed if (Values are to be concatenated grouped by Name and id and then over all by id). Previous answer was

select 
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id

You can see it implemented here : SQL Fiddle Demo

MySql GROUP_CONCAT with GROUP BY every set of nth rows

You would need to group by a function of the id. Something like this:

select group_concat(id order by id)
from testt
group by floor((id - 1) / 2)

Mysql concat and group_concat

You can't have nested aggregations in a query, so you need to do the aggregation of the order products in a subquery.

And instead of CONCAT() and GROUP_CONCAT(), you can use JSON_ARRAYAGG() if you're running at least 5.7.22.

SELECT c.name, c.email, c.contact, c.registerDate, c.status,
       JSON_ARRAYAGG(JSON_OBJECT("orderId", o.orderId, "total", o.total, "payment", o.payment, "products", op.products)) AS orders
FROM t_client AS c
INNER JOIN t_order AS o ON o.email = c.email
INNER JOIN (
      SELECT op.orderId, JSON_ARRAYAGG(JSON_OBJECT("productId", p.productId, "product", p.product, "quantity", op.quantity)) AS products
      FROM t_orderproduct AS op 
      INNER JOIN t_product AS p ON p.productId = op.productId
      GROUP BY op.orderId
) AS op ON op.orderId = o.orderId
WHERE c.clientId = 1
GROUP BY c.clientId

CONCAT() inside GROUP_CONCAT() with count

You cannot nest aggregation functions (count() is in the arguments to group_conat()). One solution is to select from a nested subquery.

SELECT group_concat(status, ':', count SEPARATOR ',') rowstring
       FROM (SELECT status,
                    count(*) count
                    FROM mytable
                    GROUP BY status) x;

MySQL GROUP_CONCAT in another GROUP_CONCAT

You should use group by (and should be enough a single group_concat)

select CONCAT(data, ' (',  GROUP_CONCAT(another SEPARATOR ', '), ');') as InOneCell
from my_table  
group by data 

or you could use a subquery

select data, group_concat(my_group)
from (
  select data, group_concat(another) my_group 
  group by data
) t 
group by data 

in your case
You could use a subquery eg:

  SELECT  
  s.name, 
  s.surname, 
  gr.number AS 'group', 
  s.bitrh_date, 
  s.email, 
  s.registration_ip,
  s.registration_time,
  GROUP_CONCAT(DISTINCT CONCAT(dis.code, ' ', dis.title, ' (', t.learning, ')') SEPARATOR ';\r\n ') as learning,
  t.average,
  gr.semester,
  ref.text AS reference

  FROM t_students s
  LEFT JOIN (SELECT
    student, 
    GROUP_CONCAT(DISTINCT mark SEPARATOR ', ') learning,
    ROUND(AVG(mark), 2) AS average
    from t_grades
    group by student
    ) t on t.student = s.id
  LEFT JOIN t_groups gr
      ON s.group_id = gr.id
  LEFT JOIN t_references ref
      ON ref.author = s.id
  LEFT JOIN t_program prog
      ON gr.id = prog.group_id
  LEFT JOIN t_disciplines dis
      ON prog.discipline_id = dis.code

  GROUP BY s.id
  ORDER BY s.registration_time DESC

mySQL Group_Concat and Case when query gives error

You have several issues with your query. First, you're not closing your CONCAT with an end ), next your AS shipping instruction cannot contain a space. Next, you have [REASONORINSTRUCTIONCODE], remove the []

Take a look at the formatted query below:

SELECT
  *,
  GROUP_CONCAT(
    CASE
      WHEN `REASONORINSTRUCTIONCODE` = 'R'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",reason-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        ))
      END,
      ", "
    )
   AS reason,
  GROUP_CONCAT(
    CASE
      WHEN `REASONORINSTRUCTIONCODE` = 'S'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",shipping instruction-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        ))
      END,
      ", "
    )
   AS shipping_instruction
FROM
  `TABLE`
GROUP BY `PICKUP_NO`

mysql group_concat (sum)

It takes 2 steps:

SELECT name, GROUP_CONCAT(sum_data)
    FROM ( SELECT name, time, SUM(data) AS sum_data
              FROM tbl GROUP BY name, time ) AS x

(And I don't see "pivoting" as being relevant.)

If you need the brackets, use CONCAT("[", GROUP_CONCAT(sum_data), "]")

MYSQL WHERE field IN GROUP_CONCAT()

Join properly the tables and aggregate:

SELECT p.email,
       GROUP_CONCAT(p.name) AS parents,  
       GROUP_CONCAT(CONCAT(c.first_name, ' ', c.last_name)) AS childrens_names 
FROM parents p LEFT JOIN children c
ON c.id = p.child_id
GROUP BY p.email

If there is a case of duplicate children names then use DISTINCT inside GROUP_CONCAT():

GROUP_CONCAT(DISTINCT CONCAT(c.first_name, ' ', c.last_name)) AS childrens_names

MySQL GROUP_CONCAT chopping off half the data (sometimes)

SET SESSION group_concat_max_len = 1000000;

This will solve your problem.
Default Group concat has a max length of 1024

Some more info: https://www.namasteui.com/mysql-group_concat-maximum-length/

---

Related Topics