Home > Software engineering >  How to join rows where not all is available?
How to join rows where not all is available?

Time:10-27

How can I join some ordered (by StNr) rows in SQL when it can happen that one or two is missing? I have the following table. Let's call it Measurements:

id  DtTm         Cd    MeasNr  StNr  Var1  Var2  Var3
-----------------------------------------------------
1   2021-10-25   abc   1       2     1.5   3.7   4.6
2   2021-10-25   abc   1       3     NULL  2.6   2.8
3   2021-10-25   def   3       1     3.5   NULL  3.6
4   2021-10-25   abc   2       1     2.6   NULL  1.5
5   2021-10-25   def   3       3     NULL  2.8   3.1
6   2021-10-25   abc   2       2     3.6   2.5   2.7
7   2021-10-25   xyz   1       2     2.2   3.0   2.6
8   2021-10-25   xyz   1       3     1.8   2.0   1.9
9   2021-10-25   xyz   1       1     3.6   1.5   3.1

I want to join the rows where the code (Cd) and the Measurement number (MeasNr) are the same and prefix the variables (Var1, Var2, Var3) with their station number (StNr). In this example 3 rows belong to each other (StNr: 1, 2, 3).

The desired output:

Cd   MeasNr  st1_Var1  st1_Var2  st1_Var3  st2_Var1  st2_Var2  st2_Var3  st3_Var1  st3_Var2  st3_Var3
-----------------------------------------------------------------------------------------------------
abc  1       NULL      NULL      NULL      1.5       3.7       4.6       NULL      2.6       2.8
abc  2       2.6       NULL      1.5       3.6       2.5       2.7       NULL      NULL      NULL
def  3       3.5       NULL      3.6       NULL      NULL      NULL      NULL      2.8       3.1
xyz  1       3.6       1.5       3.1       2.2       3.0       2.6       1.8       2.0       1.9

I tried with the following JOIN:

SELECT st1.Cd, st1.MeasNr,
st1.Var1 as st1_Var1, st1.Var2 as st1_Var2, st1.Var3 as st1_Var3,
st2.Var1 as st2_Var1, st2.Var2 as st2_Var2, st2.Var3 as st2_Var3,
st3.Var1 as st3_Var1, st3.Var2 as st3_Var2, st3.Var3 as st3_Var3
FROM Measurements as st1
LEFT JOIN Measurements as st2 on (st1.Cd = st2.Cd and st1.MeasNr = st2.MeasNr) and (st2.StNr = 2)
LEFT JOIN Measurements as st3 on (st1.Cd = st3.Cd and st1.MeasNr = st3.MeasNr) and (st3.StNr = 3)
where st1.StNr = 1

However then the first measurement of "abc" is not listed (no row with StNr=1, so there's nothing to join). How can I get the first record, too? I tried FULL JOIN, too, but without any success.

This is my SELECT with FULL JOIN:

SELECT 
  CASE
    WHEN st1.DtTm IS NOT NULL THEN st1.DtTm
    WHEN st2.DtTm IS NOT NULL THEN st2.DtTm
    WHEN st3.DtTm IS NOT NULL THEN st3.DtTm
  END AS DtTime,
  CASE
    WHEN st1.Cd IS NOT NULL THEN st1.Cd
    WHEN st2.Cd IS NOT NULL THEN st2.Cd
    WHEN st3.Cd IS NOT NULL THEN st3.Cd
  END AS Cd,
  CASE
    WHEN st1.MeasNr IS NOT NULL THEN st1.MeasNr
    WHEN st2.MeasNr IS NOT NULL THEN st2.MeasNr
    WHEN st3.MeasNr IS NOT NULL THEN st3.MeasNr
  END AS MeasNr,
  st1.Var1 AS st1_Var1,
  st1.Var2 AS st1_Var2,
  st1.Var3 AS st1_Var3,
  st2.Var1 AS st2_Var1,
  st2.Var2 AS st2_Var2,
  st2.Var3 AS st2_Var3,
  st3.Var1 AS st3_Var1,
  st3.Var2 AS st3_Var2,
  st3.Var3 AS st3_Var3
  FROM (SELECT
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 1) AS st1
  FULL JOIN (SELECT
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 2) AS st2 ON (st1.Cd = st2.Cd AND st1.MeasNr = st2.MeasNr)
  FULL JOIN (SELECT
    DtTm, Cd, MeasNr, Var1, Var2, Var3
    FROM Measurements
    WHERE StNr = 3) AS st3 ON (st1.Cd = st3.Cd AND st1.MeasNr = st3.MeasNr)

With this approach I have the missing data, but not in one row.

CodePudding user response:

Maybe it is not a pretty way, but it works:

with MyTable (Cd, MeasNr, st1_Var1, st1_Var2, st1_Var3, st2_Var1, st2_Var2, st2_Var3, st3_Var1, st3_Var2, st3_Var3)
as
(
select Cd, MeasNr, Var1 as st1_Var1, Var2 as st1_Var2, Var3 as st1_Var3, null as st2_Var1, null as st2_Var2, null as st2_Var3, null as st3_Var1, null as st3_Var2, null as st3_Var3 from Measurements st1 where StNr = 1
union
select Cd, MeasNr, null, null, null, Var1 as st2_Var1, Var2 as st2_Var2, Var3 as st2_Var3, null, null, null from Measurements st1 where StNr = 2
union
select Cd, MeasNr, null, null, null, null, null, null, Var1 as st3_Var1, Var2 as st3_Var2, Var3 as st3_Var3 from Measurements st1 where StNr = 3
)
select Cd, MeasNr, sum(st1_Var1) st1_Var1, sum(st1_Var2) st1_Var2, sum(st1_Var3) st1_Var3, 
    sum(st2_Var1) st2_Var1, sum(st2_Var2) st2_Var2, sum(st2_Var3) st2_Var3, 
    sum(st3_Var1) st3_Var1, sum(st3_Var2) st3_Var2, sum(st3_Var3) st3_Var3 
from MyTable
group by Cd, MeasNr
order by Cd, MeasNr

And I paste here the code with variable table, maybe it is useful for testing:

declare @Measurements  as table (
    id int,
    DtTm datetime,
    Cd char(3),
    MeasNr int,
    StNr int,
    Var1 decimal(5,1), 
    Var2 decimal(5,1), 
    Var3 decimal(5,1)
) 

insert into @Measurements values (1, '25/10/21', 'abc', 1, 2, 1.5, 3.7, 4.6)
insert into @Measurements values (2, '25/10/21', 'abc', 1, 3, null, 2.6, 2.8)
insert into @Measurements values (3, '25/10/21', 'def', 3, 1, 3.5, null, 3.6)
insert into @Measurements values (4, '25/10/21', 'abc', 2, 1, 2.6, null, 1.5)
insert into @Measurements values (5, '25/10/21', 'def', 3, 3, null, 2.8, 3.1)
insert into @Measurements values (6, '25/10/21', 'abc', 2, 2, 3.6, 2.5, 2.7)
insert into @Measurements values (7, '25/10/21', 'xyz', 1, 2, 2.2, 3.0, 2.6)
insert into @Measurements values (8, '25/10/21', 'xyz', 1, 3, 1.8, 2.0, 1.9)
insert into @Measurements values (9, '25/10/21', 'xyz', 1, 1, 3.6, 1.5, 3.1);

with MyTable (Cd, MeasNr, st1_Var1, st1_Var2, st1_Var3, st2_Var1, st2_Var2, st2_Var3, st3_Var1, st3_Var2, st3_Var3)
as
(
select Cd, MeasNr, Var1 as st1_Var1, Var2 as st1_Var2, Var3 as st1_Var3, null as st2_Var1, null as st2_Var2, null as st2_Var3, null as st3_Var1, null as st3_Var2, null as st3_Var3 from @Measurements st1 where StNr = 1
union
select Cd, MeasNr, null, null, null, Var1 as st2_Var1, Var2 as st2_Var2, Var3 as st2_Var3, null, null, null from @Measurements st1 where StNr = 2
union
select Cd, MeasNr, null, null, null, null, null, null, Var1 as st3_Var1, Var2 as st3_Var2, Var3 as st3_Var3 from @Measurements st1 where StNr = 3
)
select Cd, MeasNr, sum(st1_Var1) st1_Var1, sum(st1_Var2) st1_Var2, sum(st1_Var3) st1_Var3, 
    sum(st2_Var1) st2_Var1, sum(st2_Var2) st2_Var2, sum(st2_Var3) st2_Var3, 
    sum(st3_Var1) st3_Var1, sum(st3_Var2) st3_Var2, sum(st3_Var3) st3_Var3 
from MyTable
group by Cd, MeasNr
order by Cd, MeasNr

CodePudding user response:

This looks like a simple case of conditional aggregation, also known as pivoting.

Since it is a multi-column pivot, it's much easier to just use MAX(CASE WHEN

Note: No joins needed

SELECT
  m.Cd,
  m.MeasNr,
  MAX(CASE WHEN StNr = 1 THEN Var1 END) AS st1_Var1,
  MAX(CASE WHEN StNr = 1 THEN Var2 END) AS st1_Var2,
  MAX(CASE WHEN StNr = 1 THEN Var3 END) AS st1_Var3,
  MAX(CASE WHEN StNr = 2 THEN Var1 END) AS st2_Var1,
  MAX(CASE WHEN StNr = 2 THEN Var2 END) AS st2_Var2,
  MAX(CASE WHEN StNr = 2 THEN Var3 END) AS st2_Var3,
  MAX(CASE WHEN StNr = 3 THEN Var1 END) AS st3_Var1,
  MAX(CASE WHEN StNr = 3 THEN Var2 END) AS st3_Var2,
  MAX(CASE WHEN StNr = 3 THEN Var3 END) AS st4_Var3
FROM Measurements m
GROUP BY
  m.Cd,
  m.MeasNr
ORDER BY
  m.Cd,
  m.MeasNr;

SQL Fiddle

CodePudding user response:

For MySQL < v8 which does not support the common table expression WITH:

  SELECT Cd, MeasNr,
         SUM(c11) AS st1_Var1, SUM(c12) AS st1_Var2, SUM(c13) AS st1_Var3,
         SUM(c21) AS st2_Var1, SUM(c22) AS st2_Var2, SUM(c23) AS st2_Var3,
         SUM(c31) AS st3_Var1, SUM(c32) AS st3_Var2, SUM(c33) AS st3_Var3
    FROM (
           SELECT DISTINCT Cd, MeasNr, 
                           Var1 as c11, Var2 as c12, Var3 as c13,
                           NULL as c21, NULL as c22, NULL as c23, 
                           NULL as c31, NULL as c32, NULL as c33
                      FROM Measurements WHERE StNr = 1
           UNION
           SELECT DISTINCT Cd, MeasNr,
                           NULL as c11, NULL as c12, NULL as c13,
                           Var1 as c21, Var2 as c22, Var3 as c23,
                           NULL as c31, NULL as c32, NULL as c33
                      FROM Measurements WHERE StNr = 2
           UNION
           SELECT DISTINCT Cd, MeasNr,
                           NULL as c11, NULL as c12, NULL as c13,
                           NULL as c21, NULL as c22, NULL as c23,
                           Var1 as c31, Var2 as c32, Var3 as c33
                      FROM Measurements WHERE StNr = 3
         ) t1
GROUP BY Cd, MeasNr
ORDER BY Cd, MeasNr
  • Related