Last active
May 20, 2021 12:14
-
-
Save ManiruzzamanAkash/662996d0f648da3863ddc402f15abf16 to your computer and use it in GitHub Desktop.
Concat multiple row data in a single row mysql Complete example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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