如何合并两个SQL查询为单表结果并指定返回字段？

我有两张SQL表，已经编写了两个独立查询获取数据，现在需要将它们合并成一个查询，得到包含指定字段的单表结果。

原查询1（零件基础信息） #

SELECT  
    Costruttore.longname AS Costruttore,  
    Parti.partnr, Parti.ordernr,    
    Parti.description1, Parti.packagingquantity, Parti.quantityunit
FROM    
    tblPart AS Parti 
LEFT OUTER JOIN 
    tblAddress AS Costruttore ON Parti.manufacturer = Costruttore.shortname 
LEFT OUTER JOIN 
    tblAddress AS Fornitore ON Parti.supplier = Fornitore.shortname
WHERE
    (Parti.id = 12757)

原查询2（自定义属性透视结果） #

SELECT * 
FROM 
    (SELECT Id, identname, val 
     FROM tblUserFreeProperty) AS SOURCETABLE
PIVOT 
    (MAX(val)
         FOR identname IN ([DSR_Mag.Gestito], 
                           [DSR_Mag.StatoProdotto], 
                           [DSR_Mag.Qta_Cassetto], 
                           [DSR_Mag.Qta_Min],
                           [DSR_Mag.UbicazioneMag],
                           [DSR_Mag.UbicazioneScaf],
                           [DSR_Mag.UbicazionePiano],
                           [DSR_Mag.UbicazioneCass])) AS PIVOTTABLE
WHERE
    id = 12757

我尝试的查询（存在问题） #

SELECT *
FROM (SELECT Id, identname, val FROM tblUserFreeProperty) AS SOURCETABLE
        PIVOT (MAX(val)
            FOR identname In (  [DSR_Mag.Gestito], 
                                [DSR_Mag.StatoProdotto],    
                                [DSR_Mag.Qta_Cassetto], 
                                [DSR_Mag.Qta_Min],
                                [DSR_Mag.UbicazioneMag],
                                [DSR_Mag.UbicazioneScaf],
                                [DSR_Mag.UbicazionePiano],
                                [DSR_Mag.UbicazioneCass])
          ) AS PIVOTTABLE
    inner JOIN tblpart as Parti ON  PIVOTTABLE.id=Parti.id 
    where PIVOTTABLE.[DSR_Mag.Qta_Cassetto]>0

这个查询能运行，但会返回tblPart的所有属性；如果在主查询里指定部分属性（比如原查询1中的Parti.partnr、Parti.ordernr等），又会丢失子查询的透视结果。需要编写一个包含Parti.id=PIVOTTABLE.id条件的子查询，同时保留需要的所有字段。

解决方案 #

将透视查询作为子查询，与原零件信息查询进行关联，明确列出所有需要的字段，既不会返回冗余字段，也能保留透视结果：

SELECT  
    Costruttore.longname AS Costruttore,  
    Parti.partnr, Parti.ordernr,    
    Parti.description1, Parti.packagingquantity, Parti.quantityunit,
    -- 列出所有透视后的自定义属性字段
    PIVOTTABLE.[DSR_Mag.Gestito],
    PIVOTTABLE.[DSR_Mag.StatoProdotto],
    PIVOTTABLE.[DSR_Mag.Qta_Cassetto],
    PIVOTTABLE.[DSR_Mag.Qta_Min],
    PIVOTTABLE.[DSR_Mag.UbicazioneMag],
    PIVOTTABLE.[DSR_Mag.UbicazioneScaf],
    PIVOTTABLE.[DSR_Mag.UbicazionePiano],
    PIVOTTABLE.[DSR_Mag.UbicazioneCass]
FROM    
    tblPart AS Parti 
LEFT OUTER JOIN 
    tblAddress AS Costruttore ON Parti.manufacturer = Costruttore.shortname 
LEFT OUTER JOIN 
    tblAddress AS Fornitore ON Parti.supplier = Fornitore.shortname
-- 将透视查询作为子查询进行关联
INNER JOIN (
    SELECT * 
    FROM 
        (SELECT Id, identname, val 
         FROM tblUserFreeProperty) AS SOURCETABLE
    PIVOT 
        (MAX(val)
             FOR identname IN ([DSR_Mag.Gestito], 
                               [DSR_Mag.StatoProdotto], 
                               [DSR_Mag.Qta_Cassetto], 
                               [DSR_Mag.Qta_Min],
                               [DSR_Mag.UbicazioneMag],
                               [DSR_Mag.UbicazioneScaf],
                               [DSR_Mag.UbicazionePiano],
                               [DSR_Mag.UbicazioneCass])) AS PIVOTTABLE
) AS PIVOTTABLE ON Parti.id = PIVOTTABLE.id
WHERE
    Parti.id = 12757
    AND PIVOTTABLE.[DSR_Mag.Qta_Cassetto] > 0

说明 #

1. 把透视逻辑封装在子查询中，通过Parti.id = PIVOTTABLE.id与零件表关联；
2. 在主查询的SELECT语句中，逐个列出需要的字段——包括原查询1的基础信息，以及透视后的所有自定义属性列；
3. 如果需要保留没有自定义属性的零件记录，可以把INNER JOIN改成LEFT OUTER JOIN，同时调整WHERE条件（比如AND (PIVOTTABLE.[DSR_Mag.Qta_Cassetto] > 0 OR PIVOTTABLE.[DSR_Mag.Qta_Cassetto] IS NULL)）。

内容的提问来源于stack exchange，提问作者Gianfranco Vrech