要解决的逆向数据场景?

2021-09-14 00:00:00 sql sql-server unpivot

所以我有一张这样的桌子

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;

相关文章