从格式化的TSQL语句中使货币值为0或0.00 NULL

Question

我有以下SQL语句

SELECT DISTINCT
ItemID,
Taxlines = STUFF((SELECT CHAR(182) + RTRIM(IsNull(TT.Code, '')) + '$' + RTRIM(IsNull(TaxLink.BaseAmount, '')) + 
'$' + RTRIM(IsNull(TaxLink.BaseQuantity, '')) + '$' + RTRIM(IsNull(TR.Code, '')) + '$' + RTRIM(IsNull(TOC.Code, '')) + 
'$' + RTRIM(IsNull(TaxLink.Amount, '')) + '$' + RTRIM(IsNull(TPM.Code, ''))
FROM 
[Declaration].[Taxline] [TaxLink] 
LEFT OUTER JOIN 
Declaration.TaxType TT ON TT.TaxTypeID = TaxLink.TypeID
LEFT OUTER JOIN 
Declaration.TaxRate TR ON TR.TaxRateID = TaxLink.RateID
LEFT OUTER JOIN 
Declaration.TaxOverrideCode TOC ON TOC.TaxOverrideCodeID = TaxLink.OverrideCodeID
LEFT OUTER JOIN 
Declaration.TaxPaymentMethod TPM ON TPM.TaxPaymentMethodID = TaxLink.PaymentMethodID
WHERE 
Tax.ItemID = ItemID 
FOR XML PATH('')), 1, 1, '') 
FROM 
[Declaration].[Taxline] Tax
WHERE 
Tax.ItemID IS NOT NULL

生成如下输出:

A00$0.00$0$F$$0.00$D¶B00$0.00$0$S$$0.00$D

我需要将任何货币值0或0.00替换为NULL或"不介意哪个"。

不包括A00或B00，但是我需要保留$分隔符

所以上面的例子应该是这样的:

A00$$$F$$$D¶B00$$0$S$$$D 

Answer 1

Select Distinct ItemID,
Taxlines = STUFF(( Select CHAR(182) + CASE WHEN TT.Code IS NULL THEN '' ELSE TT.Code END
+ '$' + RTRIM(CASE WHEN TaxLink.BaseAmount IS NULL THEN '' ELSE CAST(TaxLink.BaseAmount AS nvarchar(15)) END)
+ '$' + RTRIM(CASE WHEN TaxLink.BaseQuantity IS NULL THEN '' ELSE CAST(CAST(TaxLink.BaseQuantity AS decimal(15,3)) AS nvarchar(15)) END)
+ '$' + RTRIM(CASE WHEN TR.Code IS NULL THEN '' ELSE TR.Code END)
+ '$' + RTRIM(CASE WHEN TOC.Code IS NULL THEN '' ELSE TOC.Code END
+ '$' + RTRIM(CASE WHEN TaxLink.Amount IS NULL THEN '' ELSE CAST(TaxLink.Amount AS nvarchar(15)) END)
+ '$' + RTRIM(CASE WHEN TPM.Code IS NULL THEN '' ELSE TPM.Code END))
FROM [Declaration].[Taxline] [TaxLink]
LEFT OUTER JOIN Declaration.TaxType TT on TT.TaxTypeID = TaxLink.TypeID
LEFT OUTER JOIN Declaration.TaxRate TR on TR.TaxRateID = TaxLink.RateID
LEFT OUTER JOIN Declaration.TaxOverrideCode TOC on TOC.TaxOverrideCodeID = TaxLink.OverrideCodeID
LEFT OUTER JOIN Declaration.TaxPaymentMethod TPM on TPM.TaxPaymentMethodID = TaxLink.PaymentMethodID
WHERE Tax.ItemID = ItemID
FOR XML PATH('')
), 1, 1, '')

(声明)。[Taxline]税收