SQL Server 2008中针对动态列的算术运算符 [英] Arithmetic operators against dynamic column in SQL Server 2008
本文介绍了SQL Server 2008中针对动态列的算术运算符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在以下查询中创建表和结果:
I have query to create table and result below:
-- tblPart PartCode[NVARCHAR(50)],UnitPrice[Decimal(18,2)]
SELECT * INTO #tblPart FROM(
SELECT 'A' PartCode, '10' UnitPrice
UNION All
SELECT 'B','11'
UNION All
SELECT 'C','38'
UNION All
SELECT 'D','20'
UNION All
SELECT 'E','12')part;
-- tblPartCondition ConditionCode[NVARCHAR(50)],PercentagePrice[Decimal(18,2)]
SELECT * INTO #tblPriceCondition FROM(
SELECT 'Weekly' ConditionCode, '3' PercentagePrice
UNION All
SELECT 'Urgent','-5'
UNION All
SELECT 'Hotline','-10'
UNION All
SELECT 'Normal','0')pricecondition
SELECT PartCode,
[Weekly]=p.UnitPrice + (SELECT (CAST(c.PercentagePrice AS DECIMAL(18,2))/100) FROM #tblPriceCondition c WHERE c.ConditionCode='Weekly'),
[Urgent]=p.UnitPrice + (SELECT (CAST(c.PercentagePrice AS DECIMAL(18,2))/100) FROM #tblPriceCondition c WHERE c.ConditionCode='Urgent'),
[Hotline]=p.UnitPrice + (SELECT (CAST(c.PercentagePrice AS DECIMAL(18,2))/100) FROM #tblPriceCondition c WHERE c.ConditionCode='Hotline'),
[Normal]=p.UnitPrice + (SELECT (CAST(c.PercentagePrice AS DECIMAL(18,2))/100) FROM #tblPriceCondition c WHERE c.ConditionCode='Normal')
FROM #tblPart p
DROP TABLE #tblPart
DROP TABLE #tblPriceCondition
并在下面得到结果:
PartCode Weekly Urgent Hotline Normal
........ ...... ...... ....... ......
A 10.03 9.95 9.9 10
B 11.03 10.95 10.9 11
C 38.03 37.95 37.9 38
D 20.03 19.95 19.9 20
E 12.03 11.95 11.9 12
上面的查询基于#tblPartCondition,Pls中的已知列.知道列是否未知吗? (例如,如果用户添加新的PercentageCode,PercentagePrice). 感谢您的宝贵时间和分享.谢谢!
The query above is based on known columns in #tblPartCondition, Pls. any idea if columns were unknown? (example if user add new PercentageCode, PercentagePrice). I would appreciate your value time and share. Thanks!
推荐答案
获取数据透视列
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + ConditionCode + ']', '[' + ConditionCode + ']')
FROM (SELECT DISTINCT ConditionCode FROM #tblPriceCondition) PV
ORDER BY ConditionCode
使用CROSS JOIN
为每个PartCode
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode,' + @cols + ' FROM
(
SELECT PARTCODE,ConditionCode,
CAST(UnitPrice + CAST(PercentagePrice AS DECIMAL(18,2))/100 AS DECIMAL(18,2)) VALUE
FROM #tblPart
CROSS JOIN #tblPriceCondition
) x
PIVOT
(
MIN(VALUE)
FOR ConditionCode IN (' + @cols + ')
) p
'
EXEC SP_EXECUTESQL @query
- 单击此处以查看结果
- Click here to view result
请回复对负值的处理方式.
将更新.
Please reply to what to do on negative values.
Will update.
这篇关于SQL Server 2008中针对动态列的算术运算符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文