SQLServerのビューの作成について[追加要件]

Question

前回の質問の追加要件となります。
下記のようなテーブルがあります。
Table名：案件B一覧Table
|ID(キー)|親ID|No|状態|状態更新日
|:--|:--:|--:|
|A1|A1|0|完了|2018/12/01
|A2|A1|1|完了|2018/12/02
|B1|B1|0|完了|2018/12/05
|A3|A1|2|進行|2018/12/10
|B2|B1|1|進行|2018/12/11
|C1|C1|1|完了|2018/12/21
|C2|C1|2|削除|2019/01/10
|C3|C1|2|取消|2019/01/11
|C4|C1|2|進行|2019/01/12
|D1|D1|0|完了|2018/12/15
|D2|D1|1|削除|2018/02/15
|E1|E1|2|進行|2019/02/22

このテーブルを親IDを元に下記のようなビューを作りたいと考えています。
同じ親IDをキーとし状態を一覧表示する
案件が削除や取消された場合同じNoで新しく案件を引き継ぎます。
同じ親ID、Noの場合は状態更新日を元に最も新しいデータをViewに表示したい
View名：案件B状態確認View
|親ID|0|1|2|
|:--|:--:|--:|
|A1|完了|完了|進行
|B1|完了|進行|
|C1||完了|進行
|D1|完了|削除|
|E1|||進行

以前のご回答ですと
サブクエリは複数の値を返しました。
サブクエリが =、!=、<、<=、>、>= の後に続く場合や、
サブクエリが 1 つの式として使われる場合は複数の値は許可されません。

とエラーが出てしまう状態です。
お力添えいただけますと幸いです。

Answer 1

親ID,no毎に最新の状態更新日のデータに絞れば、後は前回質問のデータ状態になります。
with式で効率よくします。

create view 状態確認 as
with 最新案件一覧 as (
  select *
  from 案件一覧 t
  where not exists(
          select 1 from 案件一覧 
          where 親ID=t.親ID and no=t.no and 状態更新日>t.状態更新日
        )
)
select 親ID
    , (select 状態 from 最新案件一覧 where 親ID=t.親ID and No=0) as 状態0
    , (select 状態 from 最新案件一覧 where 親ID=t.親ID and No=1) as 状態1
    , (select 状態 from 最新案件一覧 where 親ID=t.親ID and No=2) as 状態2
from 最新案件一覧 t
where ID=親ID

create table 案件一覧(
 id varchar(2), 親id varchar(2), No int, 状態 varchar(4), 状態更新日 date
);
insert into 案件一覧 values
 ('A1','A1',0,'完了','2018/12/01')
,('A2','A1',1,'完了','2018/12/02')
,('B1','B1',0,'完了','2018/12/05')
,('A3','A1',2,'進行','2018/12/10')
,('B2','B1',1,'進行','2018/12/11')
,('C1','C1',1,'完了','2018/12/21')
,('C2','C1',2,'削除','2019/01/10')
,('C3','C1',2,'取消','2019/01/11')
,('C4','C1',2,'進行','2019/01/12')
,('D1','D1',0,'完了','2018/12/15')
,('D2','D1',1,'削除','2018/02/15')
,('E1','E1',2,'進行','2019/02/22')
;

Answer 2

まず、現状のエラーが出るのはビューでサブクエリを作る場合は必ず1つの答えでないといけないためです。
例に照らし合わせるとC1の2のステータスに対し、削除、取消、進行の3種類が該当し、どの値を参照していいのか分からないので、エラーを発します。

【お詫び】
以下記述していたSQLはsazi氏から回答にそぐわないとの意見をいただき、検証の結果削除しました。まだまだ勉強が必要です。

Comments

[sazi]

アプローチがずれてて、求める結果にはなりませんよ。

[sazi]

見直して、どこが違うのか分かっては欲しいと思いましたが、回答にそぐわないとは思っていません。

[FKM]

指摘ありがとうございます、そしてwith文の利用は、こちらも大いに活用させていただきます。

削除したのは、依頼者の言うとおりの結果になっていなかったためで、後の利用者の混乱を防ぐためなので、ご了解いただけたらと思います。