sql - 如何在 Informix 12.10 中将行转换为列

Question

Localcol	SA_设备A	SA_设备 B	SA_设备 C
2021-05-04 21:27:40	12	13	14
2021-05-04 21:28:00	16	17	18

Localcol	AA_设备A	AA_设备B
2021-05-04 21:27:40	34	43
2021-05-04 21:28:00	45	46

我需要将这 2 个表合并为一个，并将其显示为以下格式。

Localcol	设备	南非	AA
2021-05-04 21:27:40	设备 A	12	34
2021-05-04 21:27:40	设备 B	13	43
2021-05-04 21:27:40	设备 C	14	不适用
2021-05-04 21:28:00	设备 A	16	45
2021-05-04 21:28:00	设备 B	17	46
2021-05-04 21:28:00	设备 C	18	不适用

请帮我在 Informix 12.10 中解决这个问题。

标签： sqlpivotinformix

Answer 1

嗯。. . 我认为您应该取消透视每个表，然后加入它们：

select coalesce(t2.localcol, t1.localcol) as localcol,
       coalesce(t2.device, t1.device) as device,
       t1.SA, t2.AA
from (select Localcol, 'A' as device, AA_Device_A as AA
      from table2 t2
      union all
      select Localcol, 'B' as device, AA_Device_B
      from table2 t2
     ) t2 full join
     (select Localcol, 'A' as device, SA_Device_A as SA
      from table2 t1
      union all
      select Localcol, 'B' as device, SA_Device_B as SA
      from table2 t1
      union all
      select Localcol, 'C' as device, SA_Device_B as SA
      from table2 t1
     ) t1
     on t1.localcol = t2.localcol and t1.device = t2.device