动态数据透视表空值handel。 [英] Dynamic Pivot Table Null value handel.

问题描述

嗨朋友们，



我使用动态数据透视表。但是有一个问题Null值句柄，

我提到编译查询

 使用 PivotData  as （ SELECT  ShapeName，
 InvoiceDetails.Purity，
 
 cast （InvoiceDetails.Purity  as   varchar （ 10  ））+ '  KRate'  as  RValue ，
 
（选择 ISNULL（总和（InvD.NetWeight  -  InvD.Stoneweight *  0 。 2 ）， 0 。 00 ）来自 InvoiceDetails InvD 其中 InvD.Invoic eId = '  Ex2832' 和 InvD。 Purity = InvoiceDetails.Purity） as  '  GoldWeight'，
 
（选择评价来自 GoldPrice 其中​​ Purity = InvoiceDetails.Purity） as  '  GoldPrice' 
  FROM 发​​票
  INNER   JOIN  InvoiceDetails  ON  Invoice.InvoiceId = InvoiceDetails.InvoiceId 
 其中 Invoice.InvoiceId = '  Ex2832'）
 
 选择 * 来自（
 选择 * 来自 PivotData 
 pivot（max（GoldPrice） for  RValue  in （[14KRate]，[18KRate]， [22KRate]）） as  pvt 
）
  as  PWD 
 
 
 
结果    -    ------  
 
 
 
 
 
 
形状名称纯度GoldWeight 14KRate 18KRate 22KRate 
 EarRings  22   4 。 946  < span class =code-keyword> NULL   NULL   46 。 12  
分面金珠18K  18   5 。 590   NULL   37 。 73   NULL  
 Gold Caps 14K  14   3 。 900   29 。 35   NULL   NULL  
三件式设置  18   5 。< span class =code-digit> 590   NULL   37 。 73   NULL  

但我的要求是-----

 ShapeName Purity GoldWeight 14KRate 18KRate 22KRate 
 EarRings 22 4.946 29.35 37.73 46.12 
切面金珠18K 18 5.590 29.35 37.73 46.12 
金帽14K 14 3.900 29.35 37.73 46.12 
三件套18 5.590 29.35 37.73 46.12 

任何身体帮助我请如何处理这个。

解决方案

现在我我得到解决方案

 使用 PivotData  as （ SELECT  ShapeName，
 InvoiceDetails.Purity，
 
 cast（InvoiceDetails.Purity  as   varchar （ 10 ））+ '  KRate'  as  RValue，
 
（选择评价来自 GoldPrice 其中​​ Purity = InvoiceDetails.Purity） as  '  GoldPrice' 
  FROM 发​​票
  INNER  < span class =code-keyword> JOIN  InvoiceDetails  ON  Invoice.InvoiceId = InvoiceDetails.InvoiceId 
 其中 Invoice.InvoiceId = '  Ex2832'）
 
 选择 ShapeName，Purity，案例 何时（ [14KRate]） IS   null  然后（ 选择 max（[14KRate]）来自 PivotData pivot（max（GoldPrice） for  RValue  in （[14KRate]）） as  pv） else  [14KRate]  end   as  [14KRate]， case  何时（[18KRate]） IS   null  然后（选择 max（[18KRate]）来自数据透视数据透视（     （[18KRate]中） ）  as  pv） else  [18KRate]  end   as  [18KRate]， case  何时（ [22KRate]） IS   null   （选择 max（[22KRate]）来自 PivotData pivot（max（GoldPrice） for  RValue  in （[22KRate]）） as  pv） else  [22KRate]  end   as  [22KRate] 来自 PivotData 
 pivot（MAX（GoldPrice） for  RValue  in （[14KRate]，[18KRate]，[22KRate]）） as  pvt 
 
 
  获取结果
 
形状名称纯度14KRate 18KRate 22KRate 
金币盖帽14K  14   29 。 35   37 。 73   46 。 12  
切面金珠18K  18   29 。 35   37 。 73   46 。 12  
三件设置  18   29 。 35   37 。 73   46 。 12  
 EarRings  22   29 。 35   37  。 73   46 。 12  

Hi Friends,

I am use a dynamic pivot table.But there is a problem Null value handle,
I am mention compiled query

With PivotData as ( SELECT ShapeName,
InvoiceDetails.Purity,

cast(InvoiceDetails.Purity as varchar(10)) + 'KRate' as RValue,

(select  ISNULL( sum (InvD.NetWeight - InvD.Stoneweight * 0.2 ),0.00)  from InvoiceDetails InvD where InvD.InvoiceId = 'Ex2832' and InvD.Purity=InvoiceDetails.Purity) as 'GoldWeight',

(select Rate from GoldPrice Where Purity=InvoiceDetails.Purity) as 'GoldPrice'
FROM Invoice
INNER JOIN   InvoiceDetails ON Invoice.InvoiceId  =InvoiceDetails.InvoiceId
Where Invoice.InvoiceId ='Ex2832' )

 select * from (
select * from PivotData
  pivot (max ( GoldPrice) for  RValue in ( [14KRate],[18KRate],[22KRate]) ) as pvt
  )
 as PWD   



Result is --------


 



ShapeName	        Purity	GoldWeight	14KRate	18KRate	22KRate
EarRings	         22	4.946	         NULL	NULL	46.12
Faceted Gold Beads 18K	 18	5.590	         NULL	37.73	NULL
Gold Caps 14K	         14	3.900	         29.35	NULL	NULL
Three Piece Set	         18	5.590	         NULL	37.73	NULL

But my requirement is -----

ShapeName	        Purity	GoldWeight	14KRate	18KRate	22KRate
EarRings	         22	4.946	         29.35	37.73	46.12
Faceted Gold Beads 18K	 18	5.590	         29.35	37.73	46.12
Gold Caps 14K	         14	3.900	         29.35	37.73	46.12
Three Piece Set	         18	5.590	         29.35	37.73	46.12

any body help me please how to handle this.

解决方案

Now i am get solution

With PivotData as ( SELECT ShapeName,
InvoiceDetails.Purity,

cast(InvoiceDetails.Purity as varchar(10)) + 'KRate' as RValue,

(select Rate from GoldPrice Where Purity=InvoiceDetails.Purity) as 'GoldPrice'
FROM Invoice
INNER JOIN   InvoiceDetails ON Invoice.InvoiceId  =InvoiceDetails.InvoiceId
Where Invoice.InvoiceId ='Ex2832' )

select ShapeName,Purity,case when  ([14KRate]) IS null   then  (select max([14KRate]) from PivotData pivot (max ( GoldPrice) for  RValue in ([14KRate]) ) as pv) else [14KRate]end  as [14KRate],case when  ([18KRate]) IS null   then  (select max([18KRate]) from PivotData pivot (max ( GoldPrice) for  RValue in ([18KRate]) ) as pv) else [18KRate]end  as [18KRate],case when  ([22KRate]) IS null   then  (select max([22KRate]) from PivotData pivot (max ( GoldPrice) for  RValue in ([22KRate]) ) as pv) else [22KRate]end  as [22KRate] from PivotData
pivot (MAX(GoldPrice) for RValue in ( [14KRate],[18KRate],[22KRate])) as pvt


And Get Result

ShapeName	          Purity	14KRate	18KRate	22KRate
Gold Caps 14K	              14	29.35	37.73	46.12
Faceted Gold Beads 18K	      18	29.35	37.73	46.12
Three Piece Set               18	29.35	37.73	46.12
EarRings	              22	29.35	37.73	46.12

这篇关于动态数据透视表空值handel。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！