需要在数据透视格式中以年份方式显示此存储过程的结果吗？ [英] Need show the Result of this stored procedure Year wise in Pivot Format ?
本文介绍了需要在数据透视格式中以年份方式显示此存储过程的结果吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
亲爱的所有人，
我需要使用枢轴显示销售价值，Saleqty，ValueGrowth，QtyGrowth年份。
请仔细阅读下面的程序让我知道我在哪里做错了。
ALTER PROCEDURE [dbo]。[ssp_MasterList_Customer_New]
@ ZoneCode INT = NULL ，
@RCode INT = NULL ，
@ SCode INT = NULL ，
@ StoreType INT = NULL ，
@ StoreCluster INT = NULL ，
@ StoreBrand INT = NULL ，
@ FDate DATETIME ，
@ TDate DATETIME ，
@ UserName VARCHAR （ 100 ）= NULL
AS
/ *
创建日期：04-DEC-2014
创建者：VIJAYA KUMAR C
* /
BEGIN
SELECT @ TDate = DATEADD（MM， 1 ， @ TDate ）;
SELECT
CList.CustomerCode，
ISNULL（CList.CustomerName，' '） AS 客户名称，
ISNULL（CList.CustomerGroup，< span class =code-string>' '） AS CustomerGroup，
CList.AllowCredit，
CList.TINNumber，
CList.DiscountPercent，
ISNULL（CList.AlternateCode，' ） AS AlternateCode，
ISNULL（CList.DoorDelivery， 0 ） AS DoorDelivery，
ISNULL（CList.CAAddress1，' '） AS CAAddress1，
ISNULL（CList.CAAddress2， ' '） AS CAAddress2，
ISNULL（CList.CAAddress3，' '） AS CAAddress3，
ISNULL（CList.CAPincode，' '） AS CAPincode，
ISNULL（ CList.CAAreaCode，' '） AS CAAreaCode，
ISNULL（C.CityName，' '） AS CACity，
ISNULL（St.StateName，' ' ） AS CAState，
ISNULL（CN.CountryName，' '） AS CACountry，
ISNULL（CList.CALandLine，' '） AS CALandLine，
CList.CAMobile，
CList.CAFax，
CList.CAEmail，
ISNULL（CList.BAPincode，' '） AS BAPincode，
ISNULL（C1.CityName，' '） AS BACity，
ISNULL（St1.StateName，' '） AS BASATED，
ISNULL （CN1.CountryName，' '） AS BACountry，
ISNULL（CList.BALandLine，' '） AS BALandLine，
CList.BAFax，
ISNULL（CList.BARemarks，' '） AS BARemarks，
ISNULL（CList.DAAddressOption，' '） AS DAAddressOption，
ISNULL（CList.DAPincode，' '） AS DAPincode，
ISNULL（C2.CityName，' '） AS DACity，
ISNULL（St2.StateName，' '） AS DAState，
ISNULL（CN2.CountryName，' '） AS DACountry，
ISNULL（CList.DALandLine，' '） AS DALandLine，
CList.DAFax，
ISNULL（CList.DARemarks，' '） AS DARemarks，
CList.IsActive，
ISNULL（S1.StoreName，' '） AS 存储，
ISNULL（R1.RegionName，' '） AS 地区，
CASE ISNULL（CList.MaritalStatus， 0 ）
WHEN 0 那么 ' < span class =code-string> Single'
WHEN 1 < span class =code-keyword> THEN ' 结婚'
WHEN 2 那么 ' 离婚'
WHEN 3 那么 ' Widow / Widower'
END AS MaritalStatus，
CASE
WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NULL 那么 ISNULL（ CONVERT （ VARCHAR （ 4 ），dobyear）， ' '）
WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NOT NULL 那么 CONVERT （ VARCHAR （ 4 ），dobyear）
WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NULL 那么 CONVERT （ VARCHAR （ 2 ），dobmonth）
WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL 那么 CONVERT （ VARCHAR （ 2 ），dobmonth）+ ' /' + CONVERT （ VARCHAR （ 4 ），dobyear）
WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NULL 那么 < span class =code-keyword> CONVERT （ VARCHAR （ 2 ），dobday）
WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NOT NULL 那么 CONVERT （ VARCHAR （ 2 ），dobday ）+ ' /' + CONVERT （ VARCHAR （ 4 ），dobyear）
WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NULL 那么 CONVERT （ VARCHAR （ 2 ），dobday）+ ' /' + CONVERT （ VARCHAR （ 2 ），dobmonth）
WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL 那么 CONVERT （ VARCHAR （ 2 ），dobday）+ ' /' + CONVERT （ VARCHAR （ 2 ），dobmonth）+ ' /' + CONVERT （ VARCHAR （ 4 ），dobyear）
END AS DateOfBirth，
CASE WHEN ISNULL（CList.DateOfAnniversary，' '）= ' ' 那么 ' ' ELSE CONVERT （ VARCHAR ，CList.DateOfAnniversary， 103 ） END DateOfAnniversary，
ISNULL（CList.CreditLimit， 0 ） AS CreditLimit，
ISNULL（CList.CreditDays， 0 ） AS CreditDays，
ISNULL（CList.PaymentTerms，' '） AS PaymentTerms，
CList.ChequePostDateLimit，
ISNULL（CList.TwitterID，' ' ） AS [Twitter ID]，
ISNULL（CList.FacebookID，' '） AS [Facebook ID]，
ISNULL（CList.AgreeToReceiveSMS， 0 ） AS AgreeToReceiveSMS，
ISNULL（CList.AgreeToReceiveEmail， 0 ） AS AgreeToReceiveEmail，
ISNULL（CList.TitleName，' '）< span class =code-keyword> AS TitleName，
ISNULL（CList.FirstName，' '） AS FirstName，
ISNULL（CList.MiddleName，' < span class =code-string>'） AS MiddleName，
ISNULL（CList.LastName，' '） AS LastName，
CASE WHEN ISNULL（CList.Gender， 0 ）= 1 那么 ' 男性' WHEN ISNULL（CList.Gender） ， 0 ）= 2 那么 ' Female' ELSE ' ' END AS 性别，
ISNULL（S.StoreName，' '） AS BaseStore，
CASE WHEN ISNULL（ CList.CreatedDate，' '）= ' ' 那么 ' ' ELSE CONVERT （ VARCHAR ，CList.CreatedDate， 103 ） END CreatedDate，
ISNULL（CC。 CLPDesc，' '） AS [< span class =code-keyword>默认 CLP卡]，
CASE WHEN ISNULL（CList.CAMobile，' '）<> ' ' AND ISNULL（CList.CAEmail ，' '）<> ' ' 那么 ' Penn Clique会员' ELSE ' ' END AS 成员
FROM
dbo.Customers CList（ NOLOCK ）
INNER JOIN dbo.Store S（ NOLOCK ） ON S.StoreCode = CList.CreatedAtStoreCode
INNER JOIN dbo.Region R（ NOLOCK ） ON R.RegionCode = S .RegionCode
LEFT JOIN dbo.Store S1（ NOLOCK ） ON S1.StoreCode = CList .StoreCode
LEFT JOIN dbo.Region R1（ NOLOCK ） ON R1.RegionCode = CList.RegionCode
LEFT JOIN dbo.City C（ NOLOCK ） ON C .CityCode = CList.CACity
LEFT JOIN dbo。[State] St（ NOLOCK ） ON St.StateCode = CList.CAState
LEFT JOIN dbo.Country CN（ NOLOCK ） ON CN.CountryCode = CList.CACountry
LEFT JOIN dbo.City C1（ NOLOCK ） ON C1.CityCode = CList.BACity
LEFT JOIN dbo。[State] St1（ NOLOCK ） ON St1.StateCode = CList .BAState
LEFT JOIN dbo.Country CN1（ NOLOCK ） ON CN1.CountryCode = CList.BACountry
LEFT JOIN dbo.City C2（ NOLOCK ） ON C2 .CityCode = CList.DACity
LEFT JOIN dbo。[State] St2（ NOLOCK ） ON St2.StateCode = CList.DAState
LEFT JOIN dbo .Country CN2（ NOLOCK ） ON CN2.CountryCode = CList.DACountry
LEFT JOIN CLPCardTrans CT（ NOLOCK ） ON CList.CustomerCode = CT.BillCustomerCode AND CONVERT （ VARCHAR ，CList.CustomerCode）= CT.BatchDescription
LEFT JOIN CLPCards CC（ NOLOCK ） ON CC.CLPCode = CT.CLPCode
WHERE
（CList.CreatedDate BETWEEN @ FDate AND @ TDate ） AND
（R.ZoneCode = @ ZoneCode 或 @ ZoneCode IS NULL ） AND
（R.RegionCode = @ RCode 或 @ RCode IS NULL ） AND
（S.StoreCode = @ SCode 或 @ SCode IS NULL ） AND
（S.StoreType = @ StoreTyp e OR @ StoreType IS NULL ） AND
（S.StoreCluster = @StoreCluster 或 @ StoreCluster IS NULL ） AND
（S.StoreBrand = @ StoreBrand 或 @ StoreBrand IS NULL ）
SELECT
1 AS CanExport
FROM
dbo.Users U WITH （ NOLOCK ）
INNER JOIN dbo.UserGroups UG WITH （ NOLOCK ） ON UG.GroupName = U. GroupName
WHERE
（U.UserName = @ UserName ） AND （UG.Permission NOT LIKE ' ％，1454％'）
UNION
SELECT
0 AS CanExport
FROM
dbo.Users U WITH （ NOLOCK ）
INNER < span class =code-keyword> JOIN dbo.UserGroups UG WITH （ NOLOCK ） ON UG.GroupName = U.GroupName
WHERE
（U.UserName = @ UserName ） AND （UG.Permission LIKE ' ％，1454％'）
结束
先谢谢
解决方案
请阅读OriginalGriff对该问题的评论。
另一方面......这很简单。看看例子：
DECLARE @ tmp 表（ClientId INT ，SomeDate DATETIME ，SomeValue INT ）
INSERT INTO @ tmp （ClientId，SomeDate，SomeValue）
VALUES （ 1 ，' 2011-01-01'， 100 ），（ 1 ，' 2011-02-01'， 110 ），
（ 1 ，' 2012-02-01'， 120 ）， （ 1 ，' 2012-03-01'， 130 ），
（ 1 ，' 2013-03-01'， 140 ），（ 1 ，' 2013-04-01'， 140 ），
（ 1 ，' 2014-04-21'， 160 ），（ 1 ，' 2014-05-01'， 150 ），
（ 1 ，' 2015-05-21'， 180 ），（ 1 ，< span class =code-string >' 2015-06-01'， 170 ），
（ 2 ，' 2012-10-01'， 800 ），（ 2 ，' 2012-11-11'， 80 ），
（ 2 ，' 2013-09-01'， 1000 ），（ 2 ，' 2013-10-01'， 100 ），
（ 2 ，' 2014-08-01'， 1200 ），（ 2 ，' 2014 -09-01'，120 ），
（ 2 ，' 2015-07-01'， 1400 ），（ 2 ，' 2015-08-01'， 140 ）
SELECT ClientId，[ 2011 ]，[ 2012 ]，[ 2013 ]，[ 2014 ]，[ 2015 ]
FROM （
SELECT ClientId，YEAR（SomeDate） AS YearOfSomeDate，SomeValue
FROM @ tmp
） AS DT
PIVOT（SUM（ SomeValue） FOR YearOfSomeDa te IN （[ 2011 ]，[ 2012 ]，[ 2013 ]，[ 2014 ]，[ 2015 ]）） AS PT
结果：
ClientId 2011 2012 2013 2014 2015
1 210 250 280 310 350
2 NULL 880 1100 1320 1540
Dear All,
I need to show salevalue,Saleqty,ValueGrowth,QtyGrowth year wise using pivot.
Please go through the below procedure and let me know where I have done mistake.
ALTER PROCEDURE [dbo].[ssp_MasterList_Customer_New]
@ZoneCode INT = NULL,
@RCode INT = NULL,
@SCode INT = NULL,
@StoreType INT = NULL,
@StoreCluster INT = NULL,
@StoreBrand INT = NULL,
@FDate DATETIME,
@TDate DATETIME,
@UserName VARCHAR(100) = NULL
AS
/*
Created Date : 04-DEC-2014
Created By : VIJAYA KUMAR C
*/
BEGIN
SELECT @TDate = DATEADD(MM, 1, @TDate);
SELECT
CList.CustomerCode,
ISNULL(CList.CustomerName,'') AS CustomerName,
ISNULL(CList.CustomerGroup,'') AS CustomerGroup,
CList.AllowCredit,
CList.TINNumber,
CList.DiscountPercent,
ISNULL(CList.AlternateCode,'') AS AlternateCode,
ISNULL(CList.DoorDelivery,0) AS DoorDelivery,
ISNULL(CList.CAAddress1,'') AS CAAddress1,
ISNULL(CList.CAAddress2,'') AS CAAddress2,
ISNULL(CList.CAAddress3,'') AS CAAddress3,
ISNULL(CList.CAPincode,'') AS CAPincode,
ISNULL(CList.CAAreaCode,'') AS CAAreaCode,
ISNULL(C.CityName,'') AS CACity,
ISNULL(St.StateName,'') AS CAState,
ISNULL(CN.CountryName,'') AS CACountry,
ISNULL(CList.CALandLine,'') AS CALandLine,
CList.CAMobile,
CList.CAFax,
CList.CAEmail,
ISNULL(CList.BAPincode,'') AS BAPincode,
ISNULL(C1.CityName,'') AS BACity,
ISNULL(St1.StateName,'') AS BAState,
ISNULL(CN1.CountryName,'') AS BACountry,
ISNULL(CList.BALandLine,'') AS BALandLine,
CList.BAFax,
ISNULL(CList.BARemarks,'') AS BARemarks,
ISNULL(CList.DAAddressOption,'') AS DAAddressOption,
ISNULL(CList.DAPincode,'') AS DAPincode,
ISNULL(C2.CityName,'') AS DACity,
ISNULL(St2.StateName,'') AS DAState,
ISNULL(CN2.CountryName,'') AS DACountry,
ISNULL(CList.DALandLine,'') AS DALandLine,
CList.DAFax,
ISNULL(CList.DARemarks,'') AS DARemarks,
CList.IsActive,
ISNULL(S1.StoreName,'') AS Store,
ISNULL(R1.RegionName,'') AS Region,
CASE ISNULL(CList.MaritalStatus,0)
WHEN 0 THEN 'Single'
WHEN 1 THEN 'Married'
WHEN 2 THEN 'Divorcee'
WHEN 3 THEN 'Widow/Widower'
END AS MaritalStatus,
CASE
WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NULL THEN ISNULL(CONVERT(VARCHAR(4),dobyear),'')
WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(4),dobyear)
WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobmonth)
WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobmonth) + '/' + CONVERT(VARCHAR(4),dobyear)
WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobday)
WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(4),dobyear)
WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(2),dobmonth)
WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(2),dobmonth) + '/' + CONVERT(VARCHAR(4),dobyear)
END AS DateOfBirth,
CASE WHEN ISNULL(CList.DateOfAnniversary,'') = '' THEN '' ELSE CONVERT(VARCHAR,CList.DateOfAnniversary,103) END DateOfAnniversary,
ISNULL(CList.CreditLimit,0) AS CreditLimit,
ISNULL(CList.CreditDays,0) AS CreditDays,
ISNULL(CList.PaymentTerms,'') AS PaymentTerms,
CList.ChequePostDateLimit,
ISNULL(CList.TwitterID,'') AS [Twitter ID],
ISNULL(CList.FacebookID,'') AS [Facebook ID],
ISNULL(CList.AgreeToReceiveSMS,0) AS AgreeToReceiveSMS,
ISNULL(CList.AgreeToReceiveEmail,0) AS AgreeToReceiveEmail,
ISNULL(CList.TitleName,'') AS TitleName,
ISNULL(CList.FirstName,'') AS FirstName,
ISNULL(CList.MiddleName,'') AS MiddleName,
ISNULL(CList.LastName,'') AS LastName,
CASE WHEN ISNULL(CList.Gender,0) = 1 THEN 'Male' WHEN ISNULL(CList.Gender,0) = 2 THEN 'Female' ELSE '' END AS Gender,
ISNULL(S.StoreName,'') AS BaseStore,
CASE WHEN ISNULL(CList.CreatedDate,'') = '' THEN '' ELSE CONVERT(VARCHAR,CList.CreatedDate,103) END CreatedDate,
ISNULL(CC.CLPDesc,'') AS [Default CLP Card],
CASE WHEN ISNULL(CList.CAMobile,'') <> '' AND ISNULL(CList.CAEmail,'') <> '' THEN 'Penn Clique Member' ELSE '' END AS Member
FROM
dbo.Customers CList (NOLOCK)
INNER JOIN dbo.Store S (NOLOCK) ON S.StoreCode = CList.CreatedAtStoreCode
INNER JOIN dbo.Region R (NOLOCK) ON R.RegionCode = S.RegionCode
LEFT JOIN dbo.Store S1 (NOLOCK) ON S1.StoreCode = CList.StoreCode
LEFT JOIN dbo.Region R1 (NOLOCK) ON R1.RegionCode = CList.RegionCode
LEFT JOIN dbo.City C (NOLOCK) ON C.CityCode = CList.CACity
LEFT JOIN dbo.[State] St (NOLOCK) ON St.StateCode = CList.CAState
LEFT JOIN dbo.Country CN (NOLOCK) ON CN.CountryCode = CList.CACountry
LEFT JOIN dbo.City C1 (NOLOCK) ON C1.CityCode = CList.BACity
LEFT JOIN dbo.[State] St1 (NOLOCK) ON St1.StateCode = CList.BAState
LEFT JOIN dbo.Country CN1 (NOLOCK) ON CN1.CountryCode = CList.BACountry
LEFT JOIN dbo.City C2 (NOLOCK) ON C2.CityCode = CList.DACity
LEFT JOIN dbo.[State] St2 (NOLOCK) ON St2.StateCode = CList.DAState
LEFT JOIN dbo.Country CN2 (NOLOCK) ON CN2.CountryCode = CList.DACountry
LEFT JOIN CLPCardTrans CT (NOLOCK) ON CList.CustomerCode = CT.BillCustomerCode AND CONVERT(VARCHAR,CList.CustomerCode) = CT.BatchDescription
LEFT JOIN CLPCards CC (NOLOCK) ON CC.CLPCode = CT.CLPCode
WHERE
(CList.CreatedDate BETWEEN @FDate AND @TDate) AND
(R.ZoneCode = @ZoneCode OR @ZoneCode IS NULL) AND
(R.RegionCode = @RCode OR @RCode IS NULL) AND
(S.StoreCode = @SCode OR @SCode IS NULL) AND
(S.StoreType = @StoreType OR @StoreType IS NULL) AND
(S.StoreCluster = @StoreCluster OR @StoreCluster IS NULL) AND
(S.StoreBrand = @StoreBrand OR @StoreBrand IS NULL)
SELECT
1 AS CanExport
FROM
dbo.Users U WITH (NOLOCK)
INNER JOIN dbo.UserGroups UG WITH (NOLOCK) ON UG.GroupName = U.GroupName
WHERE
(U.UserName = @UserName) AND (UG.Permission NOT LIKE '%,1454%')
UNION
SELECT
0 AS CanExport
FROM
dbo.Users U WITH (NOLOCK)
INNER JOIN dbo.UserGroups UG WITH (NOLOCK) ON UG.GroupName = U.GroupName
WHERE
(U.UserName = @UserName) AND (UG.Permission LIKE '%,1454%')
END
Thanks in Advance
解决方案
Please, read OriginalGriff's comment to the question.
On the other side... It's quite simple. Have a look at example:
DECLARE @tmp TABLE(ClientId INT, SomeDate DATETIME, SomeValue INT) INSERT INTO @tmp (ClientId, SomeDate, SomeValue) VALUES(1, '2011-01-01', 100),(1, '2011-02-01', 110), (1, '2012-02-01', 120), (1, '2012-03-01', 130), (1, '2013-03-01', 140), (1, '2013-04-01', 140), (1, '2014-04-21', 160), (1, '2014-05-01', 150), (1, '2015-05-21', 180), (1, '2015-06-01', 170), (2, '2012-10-01', 800), (2, '2012-11-11', 80), (2, '2013-09-01', 1000), (2, '2013-10-01', 100), (2, '2014-08-01', 1200), (2, '2014-09-01', 120), (2, '2015-07-01', 1400), (2, '2015-08-01', 140) SELECT ClientId, [2011], [2012], [2013], [2014], [2015] FROM ( SELECT ClientId, YEAR(SomeDate) AS YearOfSomeDate, SomeValue FROM @tmp ) AS DT PIVOT(SUM(SomeValue) FOR YearOfSomeDate IN([2011], [2012], [2013], [2014], [2015])) AS PT
Result:
ClientId 2011 2012 2013 2014 2015 1 210 250 280 310 350 2 NULL 880 1100 1320 1540
For dynamic version of pivoted columns, please use Google[^].
这篇关于需要在数据透视格式中以年份方式显示此存储过程的结果吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
