Arithmetic operators versus dynamic column in SQL Server 2008

Question

I have a query to create a table and the 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 the 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 above query is based on well-known columns in #tblPartCondition, Pls. any idea if the columns were unknown? (example, if the user adds a new PercentageCode, PercentagePrice). I would be grateful for your time and share. Thank you

Answer 1

Get columns for summary

 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 the result.

Please answer what to do with negative values.
Will be updated.