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;
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