including case statements on a Pivot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

including case statements on a Pivot

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-02-25 : 04:42:28

HI Guys

i need some help

I would like to add the case statement (below) into my pivot table. Would appreciate any help available

SELECT
sum(case when Dim_Interchange_Tier_2.Scheme = 'visa' then 1 else 0 end) as visa,
sum(case when Dim_Interchange_Tier_2.Scheme = 'mastercard' then 1 else 0 end) as mastercard

FROM Fact_Financial_History_2 INNER JOIN
Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code

SELECT DISTINCT (fdmsaccountno) as Fdmsaccountno,
[ParentID],
[Agent_Chain_No],
[Corp_Chain_No],
[Chain_Chain_No],
external_Account_No,
DBA_Name,
Legal_Name,
PCA,
Open_Date,
[Last_Post_Date],
[First_Post_Date],
MCC_Code,
[Security Code],
coalesce ([Standard],0)as Standard,
coalesce ([CP],0)as CP,
coalesce ( [Contactless],0)as Contactless,
coalesce ([Secure eCom],0)as Secure_eCom,
coalesce ([TBA],0)as TBA,
coalesce ([CHIP],0)as CHIP,
coalesce ([MOTO],0)as MOTO,
coalesce ( [Non Secure eCom],0) as Non_Secure_eCom,
+ISNULL([Standard],0)
+ISNULL([CP],0)
+ISNULL([Contactless],0)
+ISNULL([Secure eCom],0)
+ISNULL([TBA],0)
+ISNULL([CHIP],0)
+ISNULL([MOTO],0)
+ISNULL([Non Secure eCom],0) as 'RollingSales'
FROM
(select [Fact_Financial_History_2].hst_merchnum,
o.FDMSAccountNo,
o.[ParentID],
o.[Agent_Chain_No],
o.[Corp_Chain_No],
o.[Chain_Chain_No],
o.external_Account_No,
o.DBA_Name,
o.Legal_Name,
o.PCA,
o.Open_Date,
o.[Last_Post_Date],
o.[First_Post_Date],
stg_FDMS_Merchant_Control_Data.[Security Code],
o.MCC_Code,
Dim_Interchange_Tier_2.Qualification_2,
SUM([Fact_Financial_History_2].hst_sales_amt_R12) AS [Sales]
FROM Dim_Outlet o INNER JOIN Fact_Financial_History_2 ON o.FDMSAccountNo_First9 = Fact_Financial_History_2.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
inner join stg_FDMS_Merchant_Control_Data on o.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo
group by
Fact_Financial_History_2.hst_merchnum,
o.FDMSAccountNo,
Dim_Interchange_Tier_2.Qualification_2,
o.[ParentID],
o.[Agent_Chain_No],
o.[Corp_Chain_No],
o.[Chain_Chain_No],
o.external_Account_No,
o.DBA_Name,
o.Legal_Name,
o.PCA,
o.Open_Date,
o.[Last_Post_Date],
o.[First_Post_Date],
stg_FDMS_Merchant_Control_Data.[Security Code],
o.MCC_Code
)as p
pivot
( MAX([Sales]) FOR Qualification_2 in
([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 05:54:52

can you show how exactly is your sample data and also the required output after adding columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-02-25 : 06:25:03

HI Visakh16

These are the columns the pivot is currently producing

Fdmsaccountno
ParentID
Agent_Chain_No
Corp_Chain_No
Chain_Chain_No
external_Account_No
DBA_Name
Legal_Name
PCA
Open_Date
Last_Post_Date
First_Post_Date
MCC_Code
Security Code
Standard
CP
Contactless
Secure_eCom
TBA
CHIP
MOTO
Non_Secure_eCom
RollingSales

However i want to produce the following columns

Fdmsaccountno
ParentID
Agent_Chain_No
Corp_Chain_No
Chain_Chain_No
external_Account_No
DBA_Name
Legal_Name
PCA
Open_Date
Last_Post_Date
First_Post_Date
MCC_Code
Security Code
Standard
CP
Contactless
Secure_eCom
TBA
CHIP
MOTO
Non_Secure_eCom
RollingSales
Mastercard
Visa

Each row should be distinct (meaning that no Fdmsaccountno can be duplicated within the pivot)

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-02-25 : 17:47:15

Bump thread

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 00:46:19

without seeing some data its hard to suggest. can you show some sample data with required output as requested.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources