将两个数据透视表合并（SQL Server）

Question

我将尝试交叉连接两个数据透视表。

--第一个数据透视表查询获取药品名称列，并生成药品ID的行值。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(drug) 
                    from _app_drugs
                    group by drug, drug_id,order_number
                    order by order_number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select drug_id, drug
                from _app_drugs
            ) x
            pivot 
            (
                max(drug_id)
                for drug in (' + @cols + N')
            ) p'

exec sp_executesql @query;

--The second pivot table simply gets the signature_labels and displays the label name in the column and displays the label_id as the row value.

DECLARE @cols AS NVARCHAR(MAX),@scols as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(signature_label) 
                    from _app_signature_labels
                    WHERE _app_signature_labels.isactive=1
                    group by signature_label_id, signature_label,ordernumber
                    order by ordernumber
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select signature_label_id,signature_label
                from _app_signature_labels              
            ) x
            pivot 
            (
                max(signature_label_id)
                for signature_label in (' + @cols + N')
            ) p'

exec sp_executesql @query;

现在我只需要知道如何将这两个数据透视表合并成一个表格...它们没有共同的字段，也不需要。

有人能帮我解决这个问题吗？ 谢谢

- D Munson

---

您需要根据每个数据透视表提供一些示例数据和所需的输出结果。 - gotqn

它们没有共同的字段，也不需要一个。那你为什么要尝试将它们合并呢？这是为了报告吗？（如果是，请使用子报表） - Paul Maxwell

这里的另一个问题是，每个查询可能具有不同数量的列，因此，除非您找到一种确保两个查询中的列数相同的方法，否则您将无法使用UNION ALL。 - Paul Maxwell

Answer 1

您可以执行两个单独的动态SQL语句到两个全局临时表中，然后将结果合并，就像这样：

IF object_id('tempdb..##tmpResult1') IS NOT NULL
BEGIN
    DROP TABLE ##tmpResult1
END

IF object_id('tempdb..##tmpResult2') IS NOT NULL
BEGIN
    DROP TABLE ##tmpResult2
END

DECLARE @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @query2  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(drug) 
                    from _app_drugs
                    group by drug, drug_id,order_number
                    order by order_number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' INTO ##tmpResult1 from 
             (
                select drug_id, drug
                from _app_drugs
            ) x
            pivot 
            (
                max(drug_id)
                for drug in (' + @cols + N')
            ) p'

exec sp_executesql @query;

--The second pivot table simply gets the signature_labels and displays the label name in the column and displays the label_id as the row value.

Select @cols2 = STUFF((SELECT ',' + QUOTENAME(signature_label) 
                    from _app_signature_labels
                    WHERE _app_signature_labels.isactive=1
                    group by signature_label_id, signature_label,ordernumber
                    order by ordernumber
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query2 = N'SELECT ' + @cols2 + N' INTO ##tmpResult2 from 
             (
                select signature_label_id,signature_label
                from _app_signature_labels              
            ) x
            pivot 
            (
                max(signature_label_id)
                for signature_label in (' + @cols2 + N')
            ) p'

exec sp_executesql @query2;

SELECT * FROM ##tmpResult1 t1 
  INNER JOIN ##tmpResult2 t2 ON t1.ordernumber = t2.ordernumber

这也是解决大型PIVOT查询时出现错误的一种方法：内部错误：已达到表达式服务限制。请查找查询中可能存在的复杂表达式，并尝试简化它们。

- smoore4