62,073
社区成员
发帖
与我相关
我的任务
分享
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-05 16:43:40
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (GroupItem INT,SubItem NUMERIC(2,1),[desc] VARCHAR(5),currency VARCHAR(3),amount NUMERIC(6,2))
INSERT INTO [tb]
SELECT 1,1.1,'TEST','RMB',4232.23 UNION ALL
SELECT 1,1.1,'TEST2','HKD',3343.1 UNION ALL
SELECT 1,1.2,'TEST','RMB',3434.3
--编写存储过程:
GO
CREATE PROC dbo.DynamicRowToCol
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000);
SET @sql = N'';
SELECT
@sql = @sql + N',SUM(CASE WHEN currency=''' + currency
+ N''' THEN amount ELSE 0 END) AS [' + currency + N']'
FROM (SELECT DISTINCT currency FROM tb) AS A;
SET @sql = N'SELECT GroupItem,SubItem,MIN([desc]) AS [desc]' + @sql
+ N' FROM tb GROUP BY GroupItem,SubItem';
EXEC sp_executesql @sql;
GO
EXEC dbo.DynamicRowToCol;
GO
DROP TABLE tb;
DROP PROC dbo.DynamicRowToCol;
select GroupItem,SubItem,
RMB =sum(case when currency='RMB' then amount else 0 end),
HKD =sum(case when currency='HKD' then amount else 0 end)
from tb
group by GroupItem,SubItem