如何在一个查询中联接两个数据透视 [英] How to Join two Pivot in one Query

问题描述

我在将两个数据透视表合并到一个查询中时遇到问题.希望能尽快得到大家的建议.

例如，

Hi,
I''m facing a problem to join two pivot in one query. I hope that can get advise from all of you as soon as possible.

For example,

SELECT itemcode,posdescription as [Description],
isnull([HQ],0) as [HQ],isnull([V002],0) as [V002]
FROM (SELECT a.itemcode,a.posdescription,a.trsoutlet,a.amount,a.quantity
from vpos_eod_billdetails as a Left Join Vpos_mt_Outlet as b
on a.TrsOutlet=b.OutletCode where a.voidstatus = 0)ps
PIVOT (SUM(amount) FOR trsoutlet in ([HQ],[V002])) AS pvt


SELECT itemcode,posdescription as [Description],
isnull([HQ],0) as [HQ],isnull([V002],0) as [V002]
FROM (SELECT a.itemcode,a.posdescription,a.trsoutlet,a.amount,a.quantity
from vpos_eod_billdetails as a Left Join Vpos_mt_Outlet as b
on a.TrsOutlet=b.OutletCode where a.voidstatus = 0)ps
PIVOT ( SUM(quantity) FOR trsoutlet in ([HQ],[V002])) AS pvt

如何将这两个数据透视查询合并到一个查询中，并得到如下所示的结果:
项目代码|描述|总部数量|总部数量| V002数量| V002数量


谢谢.

How to join this two pivot query in one query and get the result show as below:
itemcode|Description|HQ Amount|HQ Quantity|V002 Amount|V002 Quantity


Thank you.

推荐答案

如果结果不重叠，则对查询使用并集:

If the results don''t overlap then use union on the queries:

select * from pvt1...
union
select * from pvt2...

如果结果重叠，则在单个查询中选择数据，然后进行数据透视:

If the results overlap select the data in a single query then pivot:

select * from 
(
select * from table1...
union
select * from table2...
)as t pivot...

这篇关于如何在一个查询中联接两个数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！