我需要从详细信息表中获得(可能是几个)列的单个表。详细信息表基本上具有以下列:
DetailID,
MasterID (foreign key to master ID),
DetailName (nvarchar()),
DetailValue (nvarchar())
到目前为止,我通过使用:
成功了SELECT Master.ID,
(SELECT DetailValue FROM Detail WHERE Master.ID=Detail.MasterID AND DetailName='Name1') as Detail1,
(SELECT DetailValue FROM Detail WHERE Master.ID=Detail.MasterID AND DetailName='Name2') as Detail2
FROM Master
,但是当我添加更多详细信息时,这会很糟糕。
这样做的有效方法是什么?
如果已知细节名称,则可以使用条件聚合:
SELECT m.ID,
MAX(CASE WHEN d.DetailName='Name1' THEN d.DetailValue END) as detail1,
MAX(CASE WHEN d.DetailName='Name2' THEN d.DetailValue END) as detail2,
...
FROM Master m
JOIN Detail d
ON m.ID=d.MasterID
GROUP BY m.ID