Databases
StackOverflow2013
Postsdbo
POmysql: merge two records into one
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql: merge two records into one
    primarykey
    data
    text
    <p>I have the below result set from my query (which has a UNION of two SELECT queries)</p> <pre><code>app_id transaction_id mobile_no user_input1 user_input3 user_input17 user_input110 user_input61 user_input62 104 731 918087318987 Welcome To Onida~Home Appliances Home Appliances~Washing Machine Washing Machine~Fully Automatic Fully Automatic~SPARKLE 65S NULL NULL 104 731 918087318987 NULL NULL NULL NULL Quantity ~2 Amount~2 </code></pre> <p>I want to merge these two records into 1 row</p> <pre><code>app_id transaction_id mobile_no user_input1 user_input3 user_input17 user_input110 user_input61 user_input62 104 731 918087318987 Welcome To Onida~Home Appliances Home Appliances~Washing Machine Washing Machine~Fully Automatic Fully Automatic~SPARKLE 65S Quantity ~2 Amount~2 </code></pre> <p>I can provide my SQL query but it is too long. Will provide if required.</p> <p><strong>SQLSchema:</strong></p> <pre><code>create table `trn_user_log` ( `app_id` int (11), `transaction_id` int (11), `mobile_no` varchar (45), `node_id` bigint (20), `customer_attribute` varchar (150), `entered_value` varchar (150) ); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','103','Welcome To Onida','2'); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','105','Home Appliances','1'); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','119','Washing Machine','1'); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','121','Fully Automatic','2'); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','169','Quantity ','2'); insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','170','Amount','2'); create table `mst_node` ( `app_id` int (11), `node_id` bigint (20), `parent_node_id` bigint (20), `display_seq` tinyint (4), `display_text` varchar (540), `customer_attribute` varchar (150) ); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','103',NULL,'1','Welcome To Onida','Welcome To Onida'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','105','103','2','Home Appliances','Home Appliances'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','119','105','1','Washing Machine','Washing Machine'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','121','119','1','Fully Automatic','Fully Automatic'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','124','121','2','SPARKLE 65S ','SPARKLE 65S '); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','125','121','3','Sparkle 65X','Sparkle 65X'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','126','121','4','Sparkle 62P','Sparkle 62P'); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','169','124','1','Quantity ','Quantity '); insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`, `display_seq`, `display_text`, `customer_attribute`) values('104','170','124','2','Amount','Amount'); </code></pre> <p><strong>My SQL Query:</strong></p> <pre><code>SELECT * FROM ( SELECT T1.app_id, T1.transaction_id, T1.mobile_no, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 103 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input1, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 105 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input3, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 119 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input17, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 121 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input110, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 169 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input61, CONVERT( GROUP_CONCAT( (CASE T1.node_id WHEN 170 THEN CONCAT(T1.customer_attribute, '~', T2.display_text) ELSE NULL END)) USING LATIN1) AS user_input62 FROM trn_user_log T1 INNER JOIN mst_node T2 ON T1.APP_ID = T2.APP_ID WHERE T1.app_id = 104 AND T1.transaction_id = 731 AND T1.node_id = T2.parent_node_id AND T2.`display_seq` = T1.entered_value -- GROUP BY T1.app_id, T1.transaction_id, T1.mobile_no -- ORDER BY T1.node_id UNION SELECT T3.app_id, T3.transaction_id, T3.mobile_no, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 103 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input1, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 105 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input3, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 119 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input17, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 121 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input110, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 169 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input61, CONVERT( GROUP_CONCAT( (CASE T3.node_id WHEN 170 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value) ELSE NULL END)) USING LATIN1) AS user_input62 FROM trn_user_log T3 INNER JOIN mst_node T4 ON T3.APP_ID = T4.APP_ID WHERE T3.app_id = 104 AND T3.transaction_id = 731 AND T3.node_id = T4.node_id AND not exists (select 1 from mst_node b where b.parent_node_id = t4.node_id) -- GROUP BY T3.app_id, T3.transaction_id, T3.mobile_no -- ORDER BY T3.node_id )T GROUP BY T.app_id, T.transaction_id, T.mobile_no </code></pre> <p><a href="http://www.sqlfiddle.com/#!2/bfcd8/1" rel="nofollow">SQLFiddle Demo Schema and Query</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDarkKnightFan
    1. USDarkKnightFan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql: merge two records into one
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysql: merge two records into one
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs it always the case that one row will have data and the other will have NULL for the corresponding columns? If not, what should the combined row contain for those columns?
      singulars
      1. POmysql: merge two records into one
      1. USBarmar
    2. COif you see the two select queries, the columns which will return Null in the first queries will have values in the second query and vice versa.. I want to merge these two records into one so ideally there will be no NULL values in the final result
      singulars
      1. POmysql: merge two records into one
      1. USDarkKnightFan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload