枢轴sql与总数
问题描述:
现在我有一个查询,让我这个;枢轴sql与总数
Transaction Type Product 1 (£) Product 2 (£) Product 3 (£)
Credit Card 1 739.02 920.70
Debit Card 3 987.34 170.13
Cheques/P Orders 7 4068.92 3442.00
Credit Card - Web 1 474.21 515.07
但现在我需要最后一行总计。
GRAND TOTAL 12 6269.49 5047.90
不能看到使用汇总与我的枢轴的SQL?任何想法,我可以用来获得最后一行?
DECLARE @ProductTypes AS NVARCHAR(MAX), @Query1 AS NVARCHAR(MAX);
SELECT @ProductTypes = STUFF((SELECT DISTINCT ',' + QUOTENAME([Description])
FROM [X].[dbo].[PRODUCT]
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @Query1 = '
;WITH CTE AS
(
SELECT PT.[description] AS [Transaction Type],
Sum (P.original_amount) AS [AMOUNT (£) CREDIT],
PR.[description] AS [Product Type]
FROM [X].[dbo].[Table1] P
join [X].[dbo].[Table2] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID
join [X].[dbo].[Table3] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID
join [X].[dbo].[Table4] OI on OI.ORDER_ID=SO.SITE_ORDER_ID
join [C].[dbo].Table5] PR on PR.Product_id=OI.PRODUCT_ID
group by PT.DESCRIPTION, PR.DESCRIPTION
)
SELECT *
FROM CTE AS T
PIVOT(SUM([AMOUNT (£) CREDIT]) FOR [Product Type] IN ('[email protected]+')) AS PT
'
EXEC(@Query1)
答
您可以使用ROLLUP
获得您想要的结果,你只需要改变你的代码稍微做。
您的代码将与此类似:
DECLARE @ProductTypes AS NVARCHAR(MAX),
@ProductTypesSum AS NVARCHAR(MAX),
@Query1 AS NVARCHAR(MAX);
SELECT @ProductTypes = STUFF((SELECT DISTINCT ',' + QUOTENAME([Description])
FROM [X].[dbo].[PRODUCT]
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SELECT @ProductTypesSum = STUFF((SELECT DISTINCT ', Sum(' + QUOTENAME([Description])+') as '+QUOTENAME([Description])
FROM [X].[dbo].[PRODUCT]
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @Query1 = '
;WITH CTE AS
(
SELECT PT.[description] AS [Transaction Type],
Sum (P.original_amount) AS [AMOUNT (£) CREDIT],
PR.[description] AS [Product Type]
FROM [X].[dbo].[Table1] P
join [X].[dbo].[Table2] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID
join [X].[dbo].[Table3] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID
join [X].[dbo].[Table4] OI on OI.ORDER_ID=SO.SITE_ORDER_ID
join [C].[dbo].Table5] PR on PR.Product_id=OI.PRODUCT_ID
group by PT.DESCRIPTION, PR.DESCRIPTION
)
SELECT
case
when [Transaction Type] is not null
then [Transaction Type]
else ''Grand Total'' end as [Transaction Type],
'[email protected]+'
FROM CTE AS T
PIVOT
(
SUM([AMOUNT (£) CREDIT])
FOR [Product Type] IN ('[email protected]+')
) AS PT
group by [Transaction Type] with rollup'
EXEC(@Query1)
见SQL Fiddle with Demo与修改后的代码。
+0
令人敬畏的逻辑和思维。站起来鞠躬! – 2013-02-19 14:59:06
+0
你可以坐下来享受你的热情。 – Kermit 2013-02-19 16:14:48
尝试汇总,但它没有工作 – 2013-02-19 14:26:11