忽略列中的空值。枢轴和不枢轴

Question

我有下面的数据格式

我正在寻找下面的数据格式

我尝试了所有选项，但无法达到以下结果。

查询：

      create table dbo.FS_Daily_HSE_SAPS_Audit(
  formId int default 2934,
  form_Description nvarchar(50) default 'Daily_HSE_SAPS_Audit',
  submittedFormId int default 63917,
  actions nvarchar(30),
  Avalue nvarchar(30),
  ObservationDescription nvarchar(50),
  ObservationValue nvarchar(50),
  isVersion int default(0)
);

插入：

insert into dbo.FS_Daily_HSE_SAPS_Audit(actions, Avalue,
  ObservationDescription, ObservationValue) values
  ('ACT/CONDITION-2', 'Condition', NULL, NULL),
  ('OBSERVATION-DESCRIPTION-2', '', NULL, NULL),
  ('SAFETY-DEFICIENCY-2', 'Act', NULL, NULL), 
  (NULL, NULL, 'OBSERVATION-DESCRIPTION-3', cast(newid() as nvarchar(50))),
  (NULL, NULL, 'ACT/CONDITION-3', cast(newid() as nvarchar(50))),
  (NULL, NULL, 'SAFETY-DEFICIENCY-3', cast(newid() as nvarchar(50)))

;


select
  a.formId, a.form_Description, a.submittedFormId, a.actions, a.Avalue,
  b.ObservationDescription, b.ObservationValue, a.isVersion
from dbo.FS_Daily_HSE_SAPS_Audit as a
inner join dbo.FS_Daily_HSE_SAPS_Audit as b
  on right(a.actions, 1) = right(b.ObservationDescription, 1);

Answer 1

你真的需要支点操作员吗？

1)

select
  a.formId, a.form_Description, a.submittedFormId, a.actions, a.Avalue,
  b.ObservationDescription, b.ObservationValue, a.isVersion
from dbo.FS_Daily_HSE_SAPS_Audit as a
inner join dbo.FS_Daily_HSE_SAPS_Audit as b
  on right(a.actions, 1) = right(b.ObservationDescription, 1)
;

结果：

+--------+----------------------+-----------------+-----------------+-----------+---------------------------+--------------------------------------+-----------+
| formId |   form_Description   | submittedFormId |     actions     |  Avalue   |  ObservationDescription   |           ObservationValue           | isVersion |
+--------+----------------------+-----------------+-----------------+-----------+---------------------------+--------------------------------------+-----------+
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-3 | Act       | OBSERVATION-DESCRIPTION-3 | 77C81C5F-8AFD-4FC6-975F-E1C3AA85C956 |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-4 | Condition | OBSERVATION-DESCRIPTION-4 | B80E24D5-B25F-415E-A9A7-9271A466E08F |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-2 | Condition | OBSERVATION-DESCRIPTION-2 | 85F1BB30-17AD-49D8-911F-8D5170663B34 |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-5 |           | OBSERVATION-DESCRIPTION-5 | 73226A46-3ED2-48CA-91A9-B17BCFF4E5BB |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-1 | Act       | OBSERVATION-DESCRIPTION-1 | 06B125D8-FED6-41EA-8E40-04A407A73FFC |         0 |
+--------+----------------------+-----------------+-----------------+-----------+---------------------------+--------------------------------------+-----------+

2)

select
  a.formId, a.form_Description, a.submittedFormId, a.actions, a.Avalue,
  b.ObservationDescription, b.ObservationValue, a.isVersion
from dbo.FS_Daily_HSE_SAPS_Audit as a
inner join dbo.FS_Daily_HSE_SAPS_Audit as b
  on left(a.actions, charindex('-', a.actions)) =
     left(b.ObservationDescription,
          charindex('-', b.ObservationDescription))
;

结果：

+--------+----------------------+-----------------+---------------------------+-----------+---------------------------+--------------------------------------+-----------+
| formId |   form_Description   | submittedFormId |          actions          |  Avalue   |  ObservationDescription   |           ObservationValue           | isVersion |
+--------+----------------------+-----------------+---------------------------+-----------+---------------------------+--------------------------------------+-----------+
|   2934 | Daily_HSE_SAPS_Audit |           63917 | ACT/CONDITION-2           | Condition | ACT/CONDITION-3           | 516F0218-1A71-40D0-A730-318A6E4DD1E3 |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | OBSERVATION-DESCRIPTION-2 |           | OBSERVATION-DESCRIPTION-3 | 952382B3-CCDF-4889-B8FC-86A80296CAC6 |         0 |
|   2934 | Daily_HSE_SAPS_Audit |           63917 | SAFETY-DEFICIENCY-2       | Act       | SAFETY-DEFICIENCY-3       | 7310BA10-6945-4108-92CE-90CF7F00ED21 |         0 |
+--------+----------------------+-----------------+---------------------------+-----------+---------------------------+--------------------------------------+-----------+

演示。

结果表已经创建为这里。

Answer 2

最重要的是右()比较"where"条件。我使用了左连接函数。及其工作

 select
  a.formId, a.form_Description, a.submittedFormId, a.actions, a.Avalue,
  b.ObservationDescription, b.ObservationValue, a.isVersion
from dbo.FS_Daily_HSE_SAPS_Audit as a
inner join dbo.FS_Daily_HSE_SAPS_Audit as b
  on left(a.actions, len(a.actions)-1) =
     left(b.ObservationDescription, len(b.ObservationDescription)-1)
;