Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Concat multiple row data in a single row mysql Complete example
-- Approach 1: Add <br /> tag after each row
---------------------------------------------------
SET session group_concat_max_len=20000;
INSERT INTO `terms` (`id`, `category`, `country`, `language`, `menu`, `content`, `page_id`, `footer`, `key`, `en`, `fi`, `se`, `no`, `dk`, `de`, `it`, `fr`, `es`, `pl`, `al`, `ru`, `ar`, `bn`, `so`, `ku`, `vi`, `cn`, `sr`, `tr`, `order_nr`, `ja`, `uk`, `ro`, `nl`, `el`, `ind`, `th`, `pt`, `lt`, `fa`, `hi`, `status`, `created_by`, `updated_by`, `deleted_by`, `deleted_at`, `created_at`, `updated_at`)
VALUES ( 805, NULL, NULL, NULL, '0', '0', NULL, '0', 'pd805', (SELECT GROUP_CONCAT(en SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(fi SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(se SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(`no` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(`dk` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(`de` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(`it` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`fr` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT(`es` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`pl` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`al` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`ru` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`ar` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`bn` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`so` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`ku` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`vi` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`cn` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`sr` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`tr` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),'1',(SELECT GROUP_CONCAT(`ja` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`uk` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`ro` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`nl` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`el` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`ind` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`th` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`pt` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`lt` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`fa` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT(`hi` SEPARATOR '<br>') FROM terms as t WHERE id IN (811,812,809)),'1', '1', '1', '1', NULL, NULL, NULL )
-- Approach 2: Add <p> </p> tag before and after each row
---------------------------------------------------
SET session group_concat_max_len=20000;
INSERT INTO `terms` (`id`, `category`, `country`, `language`, `menu`, `content`, `page_id`, `footer`, `key`, `en`, `fi`, `se`, `no`, `dk`, `de`, `it`, `fr`, `es`, `pl`, `al`, `ru`, `ar`, `bn`, `so`, `ku`, `vi`, `cn`, `sr`, `tr`, `order_nr`, `ja`, `uk`, `ro`, `nl`, `el`, `ind`, `th`, `pt`, `lt`, `fa`, `hi`, `status`, `created_by`, `updated_by`, `deleted_by`, `deleted_at`, `created_at`, `updated_at`)
VALUES ( 805, NULL, NULL, NULL, '0', '0', NULL, '0', 'pd805', (SELECT GROUP_CONCAT('<p>', en, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', fi, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', se, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', `no`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', `dk`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', `de`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', `it`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `fr`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)), (SELECT GROUP_CONCAT('<p>', `es`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `pl`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `al`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `ru`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `ar`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `bn`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `so`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `ku`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `vi`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `cn`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `sr`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `tr`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),'1',(SELECT GROUP_CONCAT('<p>', `ja`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `uk`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `ro`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `nl`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `el`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `ind`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `th`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `pt`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `lt`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `fa`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),(SELECT GROUP_CONCAT('<p>', `hi`, '</p>' SEPARATOR '') FROM terms as t WHERE id IN (811,812,809)),'1', '1', '1', '1', NULL, NULL, NULL )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment