问题描述
所以我有一张这样的桌子
So I have a table like this
表一
Quote Ref | Product A | Product B | Product C | Product D
-----------+-----------+-------------+-----------+-----------
12 | 222333 | 4748847478 | 0 | 0
我需要使用下表找出这属于哪个 Business Group
.
I need to find out which Business Group
this belongs to using the below Table .
我对等于 0 的产品字段不感兴趣,因为报价中没有这些产品,因此没有要映射的业务组.对于此报价,产品 A 和 B 列具有非零值并且可以映射到业务组.(关键点)
I am not interested in fields for the Products that equal zero as the quote does not have those products so no Business Group to map . For this quote the Product A and B column have a non zero value and can be mapped to a business group. ( KEY POINT )
表二
Product Line | Business Group
Product A | Manfacturing
Product B | Tech Net
所以我查看表 1 中的 UNPIVOT
数据.
So I look to UNPIVOT
data in Table 1 .
SELECT [QUOTE Ref], [Product Line], [Value]
FROM
(SELECT [QUOTE Ref], [Product A], [Product B], [Product C], [Product D]
FROM [Table1]) p
UNPIVOT
([Value] FOR [Product Line]
IN ([Product A], [Product B], [Product C], [Product D] )
)AS unpvt;
表 1 的数据现在像这样
DATA for table 1 now like this
Quote Ref | Product Line | Value
-----------+--------------+------------
12 | Product A | 222333
12 | Product B | 4748847478
12 | Product C | 0
12 | Product D | 0
问题是现在 4 万行变成了 470 万行.
The problem is that 40 k rows NOW becomes 4.7 million rows .
现在我知道我不需要非透视表中 Product Line 值为零的行.如何在 unpivot 查询中删除这些条目,或者在启动 UNPIVOT 之前我可以对基表做些什么?我的数据库不够大,无法处理数据库中大约 20 个类似的表和 60 多万行.
Now I know I dont need the rows in the unpivoted table where the value for a Product Line equals zero . How can I remove these entries in the unpivot query or is there something I could do to the base table before I even start the UNPIVOT ? My database is nto big enough to cope with about 20 similar tables and 60 extra million rows in the database.
推荐答案
您应该添加条件,以删除最终结果集中具有 value = 0
的条目.您无法在生成数据的 SELECT 查询中执行此操作,因此将当前结果集作为子查询括起来,如下所示:
You should add the condition that removes the entries that have value = 0
in the final result set. You couldn't do this in the SELECT query that produces the data, so enclose your current result set as subquery like this:
;WITH Data AS
(
SELECT [QUOTE Ref], [Product Line], [Value]
FROM
(
SELECT [QUOTE Ref], [Product A], [Product B], [Product C], [Product D]
FROM [Table1]
) p
UNPIVOT
([Value] FOR [Product Line]
IN ([Product A], [Product B], [Product C], [Product D] )
)AS unpvt
)
SELECT *
FROM DATA
WHERE Value <> 0;
这篇关于要解决的逆向数据场景?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!