忽略列中的空值。樞軸和非樞軸

我有以下數據格式

我正在尋找以下數據格式

我嘗試了所有選項，但無法達到以下結果。

詢問 ：

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

您真的需要 PIVOT 運算符嗎？
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 |
+--------+----------------------+-----------------+---------------------------+-----------+---------------------------+--------------------------------------+-----------+

展示。

結果表已在此處創建。

引用自：https://dba.stackexchange.com/questions/261454