在SQL Server 2008中以转置矩阵格式获取数据后如何进行计算 [英] How to do calculation after getting data in transpose matrix format in SQL Server 2008

问题描述

我写了一个用于以转置矩阵顺序显示数据的SQL脚本。

 @ SC_SupplierCategoryID INT 
 AS 
 BEGIN 
 
 CREATE table #Category（ID INT，Category NVARCHAR（100），PlanValue INT，ActValue INT）
 
 DECLARE @ tblCnt INT，@ row INT 
 
 DECLARE @cols AS NVARCHAR（MAX），@ catcols NVARCHAR（max），
 @query AS NVARCHAR（MAX）
 
 INSERT INTO #Category（ID，Category，PlanValue）
 SELECT l.ID，l.Lookup_Value，Sum（pg.PG_TotalAntExend * pg.PG_PercMadeInOman）/ 100 AS PlanValue 
 FROM TR_ICV_PlanningGoods pg 
 INNER JOIN查找l ON l.ID = pg.PG_category AND l.Lookup_Type LIKE'产品列表'
 
 LEFT OUTER JOIN TM_Supplier_Category sc sc.SC_SupplierCategoryID = pg.SC_SupplierCategoryID 
 WHERE sc.SC_SupplierCategoryID = @SC_SupplierCategoryID 
 
 GROUP BY l.Lookup_Value，l.ID 
 
 UPDATE #Category SET ActValue = PROD.actsumval FROM #Category C INNER JOIN 
（SELECT p.prod_category AS ID，SUM（p.prod_perc_madeinoman）AS actumval FROM TR_ICV_Product p 
 
 LEFT OUTER JOIN TM_Supplier_Category sc sc.SC_SupplierCategoryID = p.mio_id 
其中sc.SC_SupplierCategoryID = @ SC_SupplierCategoryID 
 
 GROUP BY p.prod_category）PROD ON PROD.ID = C.ID 
 
 
 SELECT @cols = STUFF（（SELECT'，'+ （Lookup_Value）
 FROM Lookup其中Lookup_Type LIKE'产品列表'
 GROUP BY ID，Lookup_Value 
 ORDER BY ID 
 FOR XML PATH（''），TYPE 
 ）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）
 
 
 
 SET @ query = 
'SELECT * 
 FROM（
 SELECT 
''计划''AS类别，类别AS [cat]，
 PlanValue AS planval 
 FROM #Category 
）AS s 
 PIVOT 
（
 SUM（planval）
 FOR [cat] IN（'+ @ cols +'）
）AS pvt 
 UNION 
 SELECT * 
 FROM（
 SELECT 
''Actual''AS D etails，类别AS [cat]，
 ActValue AS actval 
 FROM #Category 
）AS s 
 PIVOT 
（
 SUM（actval）
 FOR [cat] IN（'+ @ cols +'）
）AS pvt'
 PRINT @query 
执行（@query）
 --SELECT * FROM #TempTable 
 DROP TABLE #Category 
 
 END 

OUTPUT - < br $>
类别钻孔焊接

计划102 45 
实际8 30 

我想显示另一行％总计 - 计算应该是（实际* 100）/计划，EX-（8 * 100）/102=7.84


最终输出，我想要这样

计划102 45 
实际8 30 
％总计7.84 66.66 

解决方案

参考计算总计和其他汇总


http：// technet.microsoft.com/en-us/library/bb630415(v=sql.100 ）.aspx [ ^ ]

Hi,
I have written one SQL Script for displaying data in transpose matrix order.

@SC_SupplierCategoryID INT
AS
BEGIN
	
CREATE table #Category  (ID INT,Category NVARCHAR(100), PlanValue INT, ActValue INT)
 	
DECLARE @tblCnt INT ,@row INT
 
DECLARE @cols AS NVARCHAR(MAX),@catcols NVARCHAR(max),
@query  AS NVARCHAR(MAX)

	INSERT INTO #Category(ID,Category,PlanValue)
	SELECT l.ID, l.Lookup_Value, Sum(pg.PG_TotalAntExend * pg.PG_PercMadeInOman)/100 AS PlanValue  
	FROM TR_ICV_PlanningGoods pg
	INNER JOIN Lookup l ON l.ID=pg.PG_category AND l.Lookup_Type LIKE  'List Of Products'
	
	LEFT OUTER JOIN TM_Supplier_Category sc on sc.SC_SupplierCategoryID=pg.SC_SupplierCategoryID
	WHERE sc.SC_SupplierCategoryID=@SC_SupplierCategoryID
	
	GROUP BY l.Lookup_Value,l.ID
	 
	UPDATE #Category SET ActValue = PROD.actsumval  FROM #Category C INNER JOIN
	( SELECT p.prod_category AS ID, SUM(p.prod_perc_madeinoman) AS actsumval FROM TR_ICV_Product p
	
	LEFT OUTER JOIN TM_Supplier_Category sc on sc.SC_SupplierCategoryID=p.mio_id
where sc.SC_SupplierCategoryID=@SC_SupplierCategoryID

	GROUP BY p.prod_category) PROD  ON PROD.ID=C.ID


	SELECT @cols = STUFF((SELECT ',' + (Lookup_Value) 
						FROM Lookup where Lookup_Type LIKE 'List Of Products'
						GROUP BY ID, Lookup_Value
						ORDER BY ID
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
			
	
	
	SET @query=
	'SELECT *
	FROM (
		SELECT 
			''Plan'' AS Category,Category AS [cat], 
			PlanValue AS planval 
		FROM #Category
	) AS s
	PIVOT
	(
		SUM(planval)
		FOR [cat] IN ('+@cols+')
	)AS pvt
	UNION
	SELECT *
	FROM (
		SELECT 
			''Actual'' AS Details,Category AS [cat], 
			ActValue AS actval 
		FROM #Category
	) AS s
	PIVOT
	(
		SUM(actval)
		FOR [cat] IN ('+@cols+')
	)AS pvt'
	PRINT @query
	Execute (@query)
	--SELECT * FROM #TempTable
	DROP TABLE #Category

END

OUTPUT--
Category Drilling Welding

Plan	102	45
Actual	8	30

I want to display another row % Total--Calculation should be (Actual*100)/Plan, EX-(8*100)/102=7.84

Final OUTPUT , i want like this

Plan	102	45
Actual	8	30 
% Total 7.84    66.66

解决方案

Refer Calculating Totals and Other Aggregates

http://technet.microsoft.com/en-us/library/bb630415(v=sql.100).aspx[^]

这篇关于在SQL Server 2008中以转置矩阵格式获取数据后如何进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！