Complex SQL request with double join

Question

There is two tables, first one contains variable name and its ID

VARIABLE    NAME                                GUID
1000131     AddIn_EM63Alarms_Alarm_ShotCycle
1000132     AddIn_EM63Alarms_Alarm_Status
1000133     AddIn_EM63Alarms_Alarm_Code
1000134     AddIn_EM63Alarms_Alarm_Message

Second one contains variable ID and data of those variables in one column called "STRVALUE"

VARIABLE    CALCULATION TIMESTAMP_S TIMESTAMP_MS    VALUE   STATUS      GUID    STRVALUE
1000131     0           1646404026  664             33      1078067200          1209
1000132     0           1646404026  664             122     1078067200          1
1000133     0           1646404026  664             48      1078067200          650
1000134     0           1646404026  664             61      1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404026  886             131     1078067200          1209
1000132     0           1646404026  886             220     1078067200          1
1000133     0           1646404026  886             146     1078067200          650
1000134     0           1646404026  886             159     1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404027  146             229     1078067200          1209
1000132     0           1646404027  146             318     1078067200          0
1000133     0           1646404027  146             244     1078067200          650
1000134     0           1646404027  146             257     1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404027  360             327     1078067200          1209
1000132     0           1646404027  360             416     1078067200          0
1000133     0           1646404027  360             342     1078067200          650
1000134     0           1646404027  360             355     1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404027  607             425     1078067200          1209
1000132     0           1646404027  607             514     1078067200          1
1000133     0           1646404027  607             440     1078067200          650
1000134     0           1646404027  607             453     1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404027  777             523     1078067200          1209
1000132     0           1646404027  777             612     1078067200          1
1000133     0           1646404027  777             538     1078067200          650
1000134     0           1646404027  777             551     1078067200          HOPPER TEMP.: TOL. LIM. +/-
1000131     0           1646404028  190             621     1078067200          1512
1000132     0           1646404028  190             698     1078067200          1
1000133     0           1646404028  190             636     1078067200          306
1000134     0           1646404028  190             649     1078067200          REQUEST: INSPECTION 2

I would like write SQL query which should output those four variable data in separate column like that:

timestamp_s | timestamp_ms | strvalue of 1st variable | strvalue of 2nd variable | strvalue of 3rd variable | strvalue of 4th variable

There is what i try:

select
        a.timestamp_           ,
        a.strvalue as ShotCycle,
        b.strvalue as Code
from
        (
                select
                        a.timestamp_s,
                        v.name       ,
                        a.strvalue
                from
                        IMM0190_VARIABLES as v
                inner join
                        IMM0190_AL as a
                on
                        a.variable=v.variable
                where
                        v.nAME = 'AddIn_EM63Alarms_Alarm_ShotCycle' ) as a
left join
        (
                select
                        a.timestamp_s,
                        v.name       ,
                        a.strvalue
                from
                        IMM0190_VARIABLES as v
                inner join
                        IMM0190_AL as a
                on
                        a.variable=v.variable
                where
                        v.nAME = 'AddIn_EM63Alarms_Alarm_Code' ) as b
on
        a.timestamp_s=b.timestamp_s

Therefore result is not as expected. We can observe that rows are duplicated

Timestamp_s         ShotCycle   Code
1646404026          1209        650
1646404026          1209        650
1646404026          1209        650
1646404026          1209        650 

Please suggest solution how to achieve required result.

Answer 1

This is how I resolved it.

SELECT
    Main.Time,
    Main.Shot,
    Main.Status,
    Main.Code,
    Main.Message
    from
    (SELECT 
    dateadd(S, b.TIMESTAMP_S, '1970-01-01') as Time,
    b.STRVALUE as Shot,
    c.STRVALUE as Status,
    d.STRVALUE as Code,
    e.STRVALUE as Message
      FROM
      (
      select al.[VARIABLE]
            ,al.[TIMESTAMP_S]
            ,al.[TIMESTAMP_MS]
            ,al.[STRVALUE]
    from [IMM0190_T].[dbo].[IMM0190_AL] as al
    where (al.variable = 1000131)
    ) as b
    left join
    (
    select al.[VARIABLE]
            ,al.[TIMESTAMP_S]
            ,al.[TIMESTAMP_MS]
            ,al.[STRVALUE]
    from [IMM0190_T].[dbo].[IMM0190_AL] as al
    where (al.variable = 1000132)
    )as c on b.timestamp_s=c.timestamp_s and b.TIMESTAMP_MS=c.TIMESTAMP_MS
    left join
    (
    select al.[VARIABLE]
            ,al.[TIMESTAMP_S]
            ,al.[TIMESTAMP_MS]
            ,al.[STRVALUE]
    from [IMM0190_T].[dbo].[IMM0190_AL] as al
    where (al.variable = 1000133)
    )as d on b.timestamp_s=d.timestamp_s and b.TIMESTAMP_MS=d.TIMESTAMP_MS
    left join
    (
    select al.[VARIABLE]
            ,al.[TIMESTAMP_S]
            ,al.[TIMESTAMP_MS]
            ,al.[STRVALUE]
    from [IMM0190_T].[dbo].[IMM0190_AL] as al
    where (al.variable = 1000134)
    )as e on b.timestamp_s=e.timestamp_s and b.TIMESTAMP_MS=e.TIMESTAMP_MS
    ) Main