Arithmetic operators against dynamic column in SQL Server 2008

Question

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

and got result below:

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

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!

In this case people usually suggest to build a `PIVOT` query dynamically based on actual values in your `tblPriceCondition` table. — Vladimir Baranov, Feb 03 '15 at 03:47
Thanks @Vladimir Baranov, however i have no idea about PIVOT query, could you provide some code? — Sokea, Feb 03 '15 at 04:40
http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query http://stackoverflow.com/questions/19243821/pivot-transformation-using-t-sql This should give you a good starting point — Vladimir Baranov, Feb 03 '15 at 05:49

Sarath Subramanian · Accepted Answer · 2015-02-03T08:11:46.603

1

Get columns for pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + ConditionCode + ']', '[' + ConditionCode + ']')
               FROM (SELECT DISTINCT ConditionCode FROM #tblPriceCondition) PV 
               ORDER BY ConditionCode

Use CROSS JOIN to get ConditionCode and PercentagePrice for each 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.

edited Feb 03 '15 at 08:11

answered Feb 03 '15 at 07:36

Sarath Subramanian

20,027
11
82
86

the result have to include negative values. the formula of price condition is UnitPrice+(PercentagePrice/100). the PercentagePrice could be -10, 5, .. – Sokea Feb 03 '15 at 07:50
in case i would like to add optional parameter for partcode: 'WHERE PartCode=(add)PartCode OR (add)PartCode IS NULL' under 'CROSS JOIN #tblPriceCondition', it return 0 value. any idea pls. – Sokea Feb 04 '15 at 04:31
Sorry for the late reply. Was too busy. Just give **WHERE PARTCODE ='''+@PARTCODE+'''** or **WHERE PARTCODE IS NULL** after **p** and before the closing of dynamic sql. @Sokea – Sarath Subramanian Feb 06 '15 at 14:39
tried but still return 0 value if i pass value PartCode=NULL, my logic is if pass NULL value to PartCode it will return all PartCode data. – Sokea Feb 07 '15 at 01:22
could you pls. help me out on another topic: (http://stackoverflow.com/questions/29768571/allocated-stock-qty-for-order-qty-based-on-order-type-in-sql-server-2008-stored) – Sokea Apr 22 '15 at 03:45

Arithmetic operators against dynamic column in SQL Server 2008

1 Answers1

Linked