34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH a AS (
SELECT '001' AS ISO_NO,'1A' AS Version_NO,'d' AS Designer,'c' AS checker,'ins' AS instrument,'pme' AS PME,'' AS Leader,'admin' AS ISO_Control
UNION
SELECT '002' AS ISO_NO,'1A' AS Version_NO,'dd' AS Designer,'cc' AS checker,'ins-1' AS instrument,'pme-1' AS PME,'' AS Leader,'admin' AS ISO_Control
),b AS (
SELECT '001' AS ISO_NO,'Designer' AS [status] UNION
SELECT '002' AS ISO_NO,'checker' AS [status]
)
SELECT b.*,t.itemvalue FROM b LEFT JOIN a ON b.ISO_NO=a.ISO_NO
CROSS APPLY (
VALUES('Designer',a.Designer),('checker',a.checker),('instrument',a.instrument),('PME',a.PME),('Leader',a.Leader)
) t(item,itemvalue)
WHERE t.item=b. [status]
输出结果:
ISO_NO status item value
001 Designer d
002 checker cc