SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 including case statements on a Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 02/25/2013 :  04:42:28  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/25/2013 :  05:54:52  Show Profile  Reply with Quote
can you show how exactly is your sample data and also the required output after adding columns?

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

Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 02/25/2013 :  06:25:03  Show Profile  Reply with Quote
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)
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 02/25/2013 :  17:47:15  Show Profile  Reply with Quote
Bump thread
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  00:46:19  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000