我敢肯定,有更好的方法可以做到这一点(SQL Server 2014)。
我有3栏代表客户,销售或工厂的批准。值将为“是”或“否”(第三方应用程序)。我需要将所选的“是”字段缩写为首字母缩写的单独列表;例如,如果“销售”和“客户”为“是”,则需要在报表上显示“ S / C”。
我现在已经这样做了,因为它对时间很敏感,但是我敢肯定有更好的方法:
case
when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue = 'Yes'
then 'P/S/C'
when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue = 'No'
then 'P/S'
when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue = 'Yes'
then 'P/C'
when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue = 'Yes'
then 'S/C'
when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue = 'No'
then 'P'
when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue = 'No'
then 'S'
when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue = 'Yes'
then 'C'
when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue = 'No'
then ''
end as OKBy
有人有更好的方法吗?
TIA
标记
1)第一种解决方案:您可以使用映射表
SELECT x.*, s.RetValue
FROM dbo.SomeTable x
LEFT JOIN
( -- You could insert bellow values into a temp table / @table variable
-- Warning: following pair of values (CVFieldValue, CVFieldValue2, CVFieldValue3) should be UNIQUE
VALUES
('Yes', 'Yes', 'Yes', 'P/S/C'),
('Yes', 'Yes', 'No', 'P/S'),
('Yes', 'No', 'Yes', 'P/C') --, ...
) map (CVFieldValue1, CVFieldValue2, CVFieldValue3, RetValue)
ON x.CVFieldValue1 = s.CVFieldValue1
AND x.CVFieldValue2 = s.CVFieldValue2
AND x.CVFieldValue3 = s.CVFieldValue3
2)第二种解决方案:IIF和CONCAT(SQL2012 +)
SELECT STUFF(
CONCAT(
IIF(CVFieldValue1 = 'Yes', '/P', ''),
IIF(CVFieldValue2 = 'Yes', '/S', ''),
IIF(CVFieldValue3 = 'Yes', '/C', ''),
' ' -- If you remove this line then result will be NULL when all columns have non 'Yes' values
),
1, 1, '') AS Result
,*
FROM (
VALUES
('Yes', 'Yes', 'Yes'),
('Yes', 'Yes', 'No'),
('Yes', 'No', 'Yes'), -- ... Source table
('No', 'No', 'No')
) SomeTable (CVFieldValue1, CVFieldValue2, CVFieldValue3)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句