27,579
社区成员
发帖
与我相关
我的任务
分享
------------------------------------------------------------------------
-- Author :冒牌无枪狙击手V0.2
-- Date : 2011-05-15
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
------------------------------------------------------------------------
--> Test Data: ta
If Object_Id('ta') Is Not Null
Drop Table ta
GO
Create table ta ([A] Varchar(2),[B] Varchar(5),[C] Varchar(6),[D] Int)
Go
Insert Into ta
Select 'a1','通道1','方式一',20 Union All
Select 'a2','通道2','方式一',55 Union All
Select 'a3','通道2','方式2',70 Union All
Select 'a4','通道1','方式2',90
Go
-->SQL Query:
DECLARE @s varchar(8000) ,@s1 varchar(8000)
SELECT @S = ISNULL(@S + ',','')+ '['+LTRIM([c])+']'
FROM (SELECT DISTINCT [c] FROM ta) A
SELECT @S1 = ISNULL(@S1 + '+','')+ 'isnull(['+LTRIM([c])+'],0)'
FROM (SELECT DISTINCT [c] FROM ta) A
exec('WITH T
AS
(SELECT b,'+@S+'
FROM
(SELECT b,c,d
FROM TA
) P
PIVOT
( SUM ([d])
FOR [c] IN ('+@S+')
)AS UNPVT)
SELECT *,'+@S1+' AS [SUM]
FROM t')
/*
b 方式2 方式一 SUM
----- ----------- ----------- -----------
通道1 90 20 110
通道2 70 55 125
(2 行受影响)
*/