CODEWITHSUNDEEP
sqlpostgresql
Sin Han Jinn
Sin Han Jinn

Reputation: 684

How to duplicate and merge rows through select query

I've an existing Postgresql select query output that gives me,

+------+------------+------+------+
| Type | ID         | Pass | Fail |
+------+------------+------+------+
| IQC  | ABC_IQC_R2 | 0    | 6    |
+------+------------+------+------+
| IQC  | ABC_IQC_R1 | 2    | 6    |
+------+------------+------+------+
| IQC  | ABC_IQC    | 498  | 8    |
+------+------------+------+------+

How do I duplicate the row of ID-> ABC_IQC into two while merging both R1 & R2 values into that row? (As shown below)

+------+---------+------------+------+------+--------+--------+
| Type | ID      | R_ID       | Pass | Fail | R_Pass | R_Fail |
+------+---------+------------+------+------+--------+--------+
| IQC  | ABC_IQC | ABC_IQC_R2 | 498  | 8    | 0      | 6      |
+------+---------+------------+------+------+--------+--------+
| IQC  | ABC_IQC | ABC_IQC_R1 | 498  | 8    | 2      | 6      |
+------+---------+------------+------+------+--------+--------+

The two logics I can think of is,

  1. Run through the ID to search for ABC (But I'm unsure of how to match them). Duplicate the row ABC_IQC & then merge them using Lateral Join (Still unsure how)

  2. Duplicate a column for ABC_IQC(ID column) from both R2 & R1 (now becoming R_ID). Search ID for the original ABC_IQC row and extract the value of pass and fail into both R2 & R1 row.

Here is my current query to get the initial query output,

SELECT
  split_part(NewLotID, '_', 2) AS "Type",
  LotSummary ->> 'ID' AS "ID",
  LotSummary ->> 'Pass' AS "Pass",
  LotSummary ->> 'Fail' AS "Fail"
FROM
(
  SELECT
    LotSummary,
    regexp_replace(LotSummary ->> 'ID','[- ]','_','g') AS NewLotID
.
.
.

I'm not expecting a full answer because I've hardly provided any code, just any ideas or insights that might be helpful! Thank you in advance.

Upvotes: 1

Views: 212

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you want join:

with q as (
      <your query here>
     )
select q.type, q.id, qr.id as r_id, q.pass, q.fail,
       qr.pass as r_pass, qr.fail as r_fail
from q join
     q qr
     on q.id = 'ABC_IQC' and qr.id like 'ABC_IQC_%';

You can actually generalize this:

with q as (
      <your query here>
     )
select q.type, q.id, qr.id as r_id, q.pass, q.fail,
       qr.pass as r_pass, qr.fail as r_fail
from q join
     q qr
     on q.id ~ '^[^_]+_[^_]+$' and
        qr.id like q.id || '_%';

Upvotes: 1

Related Questions