問題描述

所以我有一張這樣的桌子

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 的產品字段不感興趣，因為報價中沒有這些產品，因此沒有要映射的業(yè)務組.對于此報價，產品 A 和 B 列具有非零值并且可以映射到業(yè)務組.(關鍵點)

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 查詢中執(zhí)行此操作，因此將當前結果集作為子查詢括起來，如下所示:

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;

這篇關于要解決的逆向數據場景?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！