Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 06:25:14
|
Hey guys Quick question Is there any way to only populate the pivot that have a value in them ? eg if there is no value don’t populate that particular description ? This is my query SELECT DISTINCT FDMSAccountNo as FDMSAccountNo,[REFUND TXN CHARGE],[AUTHORISATION REQUEST],[POLLING FEE],[CHARGEBACK FEE],[ANNUAL MEMBERSHIP FEE],[MMV MONTHLY ACCESS FEE],[CASHBACK FEE],[TERMINAL FEE],[MMV MONTHLY FEE],[MONTHLY STATEMENT FEE],[FDMS LINK MONTHLY FEE],[POS PARTNER],[SOFTWARE UPGRADE],[MC AUTO BILLING UPD MTH FEE],[MONTHLY CLIENTLINE FEE],[3-TIER MONTHLY SERVICE CHARGE],[MONTHLY MAINTENANCE FEE],[JOINING FEE],[PCI DSS MANAGEMENT FEE],[PCI DSS MANAGEMENT PM],[PCI DSS INACTIVITY CHARGE],[PCI DSS NON COMPLIANCE],[MC DEBIT INTERCHANGE PLUS],[MC DEBIT IC PLUS SALES],[MC DEBIT CHIP INTERCHANGE PLUS],[MC DEBIT CHIP IC PLUS SALES],[MAESTRO SALES IC FEE],[MAESTRO CHIP SALES IC FEE],[MAESTRO INTERCHANGE FEE],[MAESTRO CHIP INTERCHANGE FEE],[SOLO INTERCHANGE FEE],[SOLO CHIP INTERCHANGE FEE],[SOLO SALES IC FEE],[SOLO CHIP SALES IC FEE],[VISA SALES IC FEE],[VISA ELECTRON SALES IC FEE],[VISA CHIP SALES IC FEE],[VISA PURCH CARD SALES IC FEE],[VISA DEBIT SALES IC FEE],[VISA DEBIT CHIP SALES IC FEE],[VISA INTERCHANGE FEE],[VISA ELECTRON INTERCHANGE FEE],[VISA CHIP INTERCHANGE FEE],[VISA PURCH CARD INTRCHG FEE],[VISA DEBIT INTERCHANGE FEE],[VISA DEBIT CHIP INTERCHG FEE],[MASTERCARD SALES IC FEE],[MASTERCARD INTERCHANGE FEE],[MC INTERCHANGE],[MC CHIP INTERCHANGE FEE],[MC PURCH CARD INTERCHANGE FEE],[MC CHIP SALES IC FEE],[MC PURCH CARD SALES IC FEE],[DINERS INTERCHANGE FEE],[M/C+MAESTRO EU ACCEPTANCE FEE],[M/C INT. RETAIL ACCEPTANCE FEE],[MAESTRO INT. ACCEPTANCE FEE],[MC DEBIT SERVICE CHARGE],[MC DEBIT REFUNDS],[MC DEBIT SALES TRANSACTION FEE],[MC DEBIT REFUND TRANSACTION],[MC DEBIT NQ SERVICE CHARGE],[MC DEBIT NQ REFUNDS],[MC DEBIT NQ SALES TRANS FEE],[MC DEBIT NQ REFUND TRANSACTION],[MC DEBIT CHIP SERVICE CHARGE],[MC DEBIT CHIP SALES TRANS FEE],[MC DEBIT CHIP REFUND TRANS FEE],[MC DEBIT CHIP NQ SERVICE CHRG],[MC DBT CHP NQ SALES TRANS],[VISA NON-QUAL SALES TRANS FEE],[VISA NON-QUAL REFUNDS TRAN FEE],[VISA DEBIT NQ SALES TRANS FEE],[VISA DEBIT NQ REFUND TRANS FEE],[VISA ELECT DR NQ SALE TRAN FEE],[VISA ELECT DR NQ RFD TRANS FEE],[MAESTRO NON-QUAL SLS TRANS FEE],[SOLO NON-QUAL SALES TRANS FEE],[VISA CHIP NQ SALES TRANS FEE],[VISA CHIP NQ REFUNDS TRANS FEE],[VISA DR CHIP NQ SALES TRAN FEE],[VISA DR CHIP NQ RFND TRANS FEE],[MAESTRO CHIP NQ SALES TRANS FE],[SOLO CHIP NQ SALES TRANS FEE],[VISA PURCH CARD NQ RFND T/FEE],[VISA NON-QUAL SERVICE CHARGE],[VISA NON-QUAL REFUNDS],[VISA NQ DEBIT SERVICE CHARGE],[VISA NON-QUAL DEBIT REFUNDS],[VISA ELECTRON NQ DR SERV CHG],[VISA ELECTRON NQ DR REFUNDS],[VISA CHIP NQ SERVICE CHARGE],[VISA CHIP NON-QUAL REFUNDS],[VISA DEBIT CHIP NQ SERVICE CHG],[MAESTRO NON-QUAL SERVICE CHG],[INTL MSTRO NQ SERVICE CHARGE],[VISA PURCHASE CARD NQ SRV CHG],[MASTERCARD NQ SERVICE CHARGE],[MASTERCARD NON-QUAL REFUNDS],[MASTERCARD CHIP NQ SRV CHRG],[MASTERCARD CHIP NQ REFUNDS],[MC PURCHASE CARD NQ SRV CHRG],[MASTERCARD NQ SALES TRANS FEE],[MASTERCARD NQ RFND TRANS FEE],[MASTERCARD CHIP NQ RFND T/FEE],[VISA PRCH NQ SRVCHG(EX BUS DR)],[DINERS SALES TRANS FEE],[VISA BUS DR CARD NQ SALE T/FEE],[VISA BUS DR CARD NQ RFND T/FEE],[VISA PRCH NQ REF TX(EX BUS DR)],[VISA BUS DR CARD NQ SRV CHG],[MC NQ SERVICE CHARGE (PREM)],[MC NON-QUAL REFUNDS (PREM)],[MC CHIP NQ SRV CHRG (PREM)],[MONTHLY INTERNET ACCESS FEE],[VAT INT MTH ACCESS FEE 17.5%],[TERMINAL RENTAL FEE],[VAT TERMINAL RENTAL FEE 17.5%],[VIRTUAL TERM TRANSACTION FEE],[VAT VIRT TERM TRAN FEE 15.0%],[MIN VIRTUAL TERMINAL TRAN FEE],[VAT MIN VIR TERM TRAN FEE15.0%],[VISA NQSERVICE CHG (CHCD)],[VISA NON.QUALREFUNDS(CHCD)],[VISA CHIP NQ SERVICE CHG(CHCD)],[VISA NON-QUAL SRV CHG EX.CHCD],[VISA NON-QUAL REFUNDS EX.CHCD],[VISA CHIP NQ SRV CHG EX.CHCD],[MC NQ SERVICE CHARGE EX. PREM],[MC NON-QUAL REFUNDS EX. PREM],[MC CHIP NQ SRV CHRG EX. PREM],[MC CHIP NQ REFUNDS EX. PREM],[VISA SERVICE CHARGE],[VISA REFUNDS],[VISA DEBIT SERVICE CHARGE],[VISA DEBIT REFUNDS],[VISA ELECTRON DR SERV CHARGE],[VISA ELECTRON DR REFUNDS],[MAESTRO SERVICE CHARGE],[MAESTRO REFUNDS],[SOLO SERVICE CHARGE],[SOLO REFUNDS],[VISA CHIP SERVICE CHARGE],[VISA CHIP REFUNDS],[VISA DEBIT CHIP SERVICE CHARGE],[MAESTRO CHIP SERVICE CHARGE],[SOLO CHIP SERVICE CHARGE],--[VISA PURCHASE CARD SERV CHRG],--[VISA PURCHASE CARD REFUNDS],[VISA COMMERCIAL CARD SERV CHRG],[VISA COMMERCIAL CARD REFUNDS],[VISA SALES TRANS FEE],[VISA REFUNDS TRANS FEE],[VISA DEBIT SALES TRANS FEE],[VISA DEBIT REFUNDS TRANS FEE],[VISA ELECTRON DR SALE TRAN FEE],[VISA ELECTRON DR RFD TRANS FEE],[MAESTRO SALES TRANS FEE],[MAESTRO REFUNDS TRANS FEE],[SOLO SALES TRANS FEE],[SOLO REFUNDS TRANS FEE],[VISA CHIP SALES TRANS FEE],[VISA CHIP REFUNDS TRANS FEE],[VISA DR CHIP SALE TRANS FEE],[VISA DR CHIP REFUNDS TRANS FEE],[VISA COMM CARD RFND T/FEE],[MAESTRO CHIP SALES TRANS FEE],[MAESTRO CHIP REFUNDS TRANS FEE],[SOLO CHIP SALES TRANS FEE],[SOLO CHIP REFUNDS TRANS FEE],[VISA PURCHASE CARD SALE T/FEE],[VISA PURCHASE CARD RFND T/FEE],[INTL MSTRO RFND TRN CNT-ED],[MASTERCARD SERVICE CHARGE],[MASTERCARD REFUNDS],[MASTERCARD CHIP SERVICE CHARGE],[MASTERCARD CHIP REFUNDS],[MC PURCHASE CARD SERV CHRG],[MC PURCHASE CARD REFUNDS],[MASTERCARD SALES TRANS FEE],[MASTERCARD REFUNDS TRANS FEE],[MASTERCARD CHIP SALE TRANS FEE],[MASTERCARD CHIP RFND TRANS FEE],[MC PURCHASE CARD SALE T/FEE],[MC PURCHASE CARD RFND T/FEE],[MC COMMERCIAL CARD RFND T/FEE],[MC COMMERCIAL CARD SERV CHRG],[MC COMMERCIAL CARD REFUNDS],[VISA PRCH SRV CHG(EX BUS DR)],[VISA PRCH REFUND (EX BUS DR)],[VISA BUS DR CARD SERV CHRG],[VISA SERVICE CHARGE EX.CHCD],[VISA REFUNDS EX.CHCD],[VISA CHIP SERVICE CHG EX.CHCD],[VISA CHIP REFUNDS EX.CHCD],[VISA BUS DR CARD SALE T/FEE],[VISA BUS DR CARD RFND T/FEE],[VISA PRCH SLS T/FEE(EX BUS DR)],[VISA PRCH REF T/FEE(EX BUS DR)],[MC SERVICE CHARGE (PREM)],[MC REFUNDS (PREM)],[MC CHIP SERVICE CHARGE (PREM)],[MC CHIP REFUNDS (PREM)],[MC REFUNDS TRANS FEE (PREM)],[MC CHIP RFND TRANS FEE (PREM)],[VISA SERVICE CHARGE(CHCD)],[VISA SERVICE CHARGE(CHCD) RFND],[VISA CHIP SERVICE CHARGE(CHCD)],[VISA PREM RFND T/FEE],[VISA REFUNDS TRANS FEE EX.CHCD],[VISA CHIP REFUND T/FEE EX.CHCD],[MC SERVICE CHARGE EX. PREM],[MC REFUNDS EX. PREM],[MC CHIP SERVICE CHG EX. PREM],[MC CHIP REFUNDS EX. PREM],[MC REFUNDS TRANS FEE EX. PREM],[MC CHIP REF TRANS FEE EX.PREM],[VISA COMM REF T/FEE(EX BUS DR)],[VISA COMM SERV CHRG(EX BUS DR)],[VISA COMM REFUNDS (EX BUS DR)],[MINIMUM MONTHLY CHARGE],[BOARDING FEE],[Authorisation Wholesale Fee],[AMEX],[CANCELLATION FEE],[FUNDING ADJUSTMENT],[DEBIT CARD DEPOSITS],[INTRCHNG REJ RESOLVED TO MERCH],[REPRESENTED DD],[TERMINAL BILLING],[FDGL Clawback],[FDGL CONFIG FEE],[FDGL Commission],[HIGH RISK UNDERWRITING FEE],[MSC ADJUSTMENT],[CREDIT ADJUSTMENT],[EBT CREDIT ADJUSTMENT],[MAESTRO CHARGEBACK REVERSAL CR],[MAESTRO CHARGEBACK CREDIT],[MAESTRO CHARGEBACK DEBIT],[MINIMUM ANNUAL SHORT FALL],[PAYMENT 1],[RECLAIMED FUNDS],[PAYMENT 3],[CHARGEBACK SUSPENSE],[SUSPENSE CLEARANCE],[ADJUSTMENT],[MASTERCARD ADJUSTMENTS],[TBA],[LETTER FEE],[NON-PAYMENT OF DIRECT DEBIT],[EXCESSIVE CHARGEBACK FEE],[OPERATIONAL GUIDANCE FEE],[COLLECTIONS FEE],[MANAGEMENT FEE],[TERMINATION FEE],[VAT TERMINAL FEE ADJUSTMENT],[TERMINAL FEE ADJUSTMENT],[MAESTRO ADJ. CUSTOMER INQUIRY],[LEGAL ENTITY ADMIN FEE],[CARD NOT PRESENT ADMIN FEE],[ADJUSTMENT],[MISC ADJUSTMENT],[PENDING INVESTIGATION],[MONTHLY RESIDENCE FEE],[Refund Wholesale fee], ISNULL([REFUND TXN CHARGE],0)+ISNULL([AUTHORISATION REQUEST],0)+ISNULL([POLLING FEE],0)+ISNULL([CHARGEBACK FEE],0)+ISNULL([ANNUAL MEMBERSHIP FEE],0)+ISNULL([MMV MONTHLY ACCESS FEE],0)+ISNULL([CASHBACK FEE],0)+ISNULL([TERMINAL FEE],0)+ISNULL([MMV MONTHLY FEE],0)+ISNULL([MONTHLY STATEMENT FEE],0)+ISNULL([FDMS LINK MONTHLY FEE],0)+ISNULL([POS PARTNER],0)+ISNULL([SOFTWARE UPGRADE],0)+ISNULL([MC AUTO BILLING UPD MTH FEE],0)+ISNULL([MONTHLY CLIENTLINE FEE],0)+ISNULL([3-TIER MONTHLY SERVICE CHARGE],0)+ISNULL([MONTHLY MAINTENANCE FEE],0)+ISNULL([JOINING FEE],0)+ISNULL([PCI DSS MANAGEMENT FEE],0)+ISNULL([PCI DSS MANAGEMENT PM],0)+ISNULL([PCI DSS INACTIVITY CHARGE],0)+ISNULL([PCI DSS NON COMPLIANCE],0)+ISNULL([MC DEBIT INTERCHANGE PLUS],0)+ISNULL([MC DEBIT IC PLUS SALES],0)+ISNULL([MC DEBIT CHIP INTERCHANGE PLUS],0)+ISNULL([MC DEBIT CHIP IC PLUS SALES],0)+ISNULL([MAESTRO SALES IC FEE],0)+ISNULL([MAESTRO CHIP SALES IC FEE],0)+ISNULL([MAESTRO INTERCHANGE FEE],0)+ISNULL([MAESTRO CHIP INTERCHANGE FEE],0)+ISNULL([SOLO INTERCHANGE FEE],0)+ISNULL([SOLO CHIP INTERCHANGE FEE],0)+ISNULL([SOLO SALES IC FEE],0)+ISNULL([SOLO CHIP SALES IC FEE],0)+ISNULL([VISA SALES IC FEE],0)+ISNULL([VISA ELECTRON SALES IC FEE],0)+ISNULL([VISA CHIP SALES IC FEE],0)+ISNULL([VISA PURCH CARD SALES IC FEE],0)+ISNULL([VISA DEBIT SALES IC FEE],0)+ISNULL([VISA DEBIT CHIP SALES IC FEE],0)+ISNULL([VISA INTERCHANGE FEE],0)+ISNULL([VISA ELECTRON INTERCHANGE FEE],0)+ISNULL([VISA CHIP INTERCHANGE FEE],0)+ISNULL([VISA PURCH CARD INTRCHG FEE],0)+ISNULL([VISA DEBIT INTERCHANGE FEE],0)+ISNULL([VISA DEBIT CHIP INTERCHG FEE],0)+ISNULL([MASTERCARD SALES IC FEE],0)+ISNULL([MASTERCARD INTERCHANGE FEE],0)+ISNULL([MC INTERCHANGE],0)+ISNULL([MC CHIP INTERCHANGE FEE],0)+ISNULL([MC PURCH CARD INTERCHANGE FEE],0)+ISNULL([MC CHIP SALES IC FEE],0)+ISNULL([MC PURCH CARD SALES IC FEE],0)+ISNULL([DINERS INTERCHANGE FEE],0)+ISNULL([M/C+MAESTRO EU ACCEPTANCE FEE],0)+ISNULL([M/C INT. RETAIL ACCEPTANCE FEE],0)+ISNULL([MAESTRO INT. ACCEPTANCE FEE],0)+ISNULL([MC DEBIT SERVICE CHARGE],0)+ISNULL([MC DEBIT REFUNDS],0)+ISNULL([MC DEBIT SALES TRANSACTION FEE],0)+ISNULL([MC DEBIT REFUND TRANSACTION],0)+ISNULL([MC DEBIT NQ SERVICE CHARGE],0)+ISNULL([MC DEBIT NQ REFUNDS],0)+ISNULL([MC DEBIT NQ SALES TRANS FEE],0)+ISNULL([MC DEBIT NQ REFUND TRANSACTION],0)+ISNULL([MC DEBIT CHIP SERVICE CHARGE],0)+ISNULL([MC DEBIT CHIP SALES TRANS FEE],0)+ISNULL([MC DEBIT CHIP REFUND TRANS FEE],0)+ISNULL([MC DEBIT CHIP NQ SERVICE CHRG],0)+ISNULL([MC DBT CHP NQ SALES TRANS],0)+ISNULL([VISA NON-QUAL SALES TRANS FEE],0)+ISNULL([VISA NON-QUAL REFUNDS TRAN FEE],0)+ISNULL([VISA DEBIT NQ SALES TRANS FEE],0)+ISNULL([VISA DEBIT NQ REFUND TRANS FEE],0)+ISNULL([VISA ELECT DR NQ SALE TRAN FEE],0)+ISNULL([VISA ELECT DR NQ RFD TRANS FEE],0)+ISNULL([MAESTRO NON-QUAL SLS TRANS FEE],0)+ISNULL([SOLO NON-QUAL SALES TRANS FEE],0)+ISNULL([VISA CHIP NQ SALES TRANS FEE],0)+ISNULL([VISA CHIP NQ REFUNDS TRANS FEE],0)+ISNULL([VISA DR CHIP NQ SALES TRAN FEE],0)+ISNULL([VISA DR CHIP NQ RFND TRANS FEE],0)+ISNULL([MAESTRO CHIP NQ SALES TRANS FE],0)+ISNULL([SOLO CHIP NQ SALES TRANS FEE],0)+ISNULL([VISA PURCH CARD NQ RFND T/FEE],0)+ISNULL([VISA NON-QUAL SERVICE CHARGE],0)+ISNULL([VISA NON-QUAL REFUNDS],0)+ISNULL([VISA NQ DEBIT SERVICE CHARGE],0)+ISNULL([VISA NON-QUAL DEBIT REFUNDS],0)+ISNULL([VISA ELECTRON NQ DR SERV CHG],0)+ISNULL([VISA ELECTRON NQ DR REFUNDS],0)+ISNULL([VISA CHIP NQ SERVICE CHARGE],0)+ISNULL([VISA CHIP NON-QUAL REFUNDS],0)+ISNULL([VISA DEBIT CHIP NQ SERVICE CHG],0)+ISNULL([MAESTRO NON-QUAL SERVICE CHG],0)+ISNULL([INTL MSTRO NQ SERVICE CHARGE],0)+ISNULL([VISA PURCHASE CARD NQ SRV CHG],0)+ISNULL([MASTERCARD NQ SERVICE CHARGE],0)+ISNULL([MASTERCARD NON-QUAL REFUNDS],0)+ISNULL([MASTERCARD CHIP NQ SRV CHRG],0)+ISNULL([MASTERCARD CHIP NQ REFUNDS],0)+ISNULL([MC PURCHASE CARD NQ SRV CHRG],0)+ISNULL([MASTERCARD NQ SALES TRANS FEE],0)+ISNULL([MASTERCARD NQ RFND TRANS FEE],0)+ISNULL([MASTERCARD CHIP NQ RFND T/FEE],0)+ISNULL([VISA PRCH NQ SRVCHG(EX BUS DR)],0)+ISNULL([DINERS SALES TRANS FEE],0)+ISNULL([VISA BUS DR CARD NQ SALE T/FEE],0)+ISNULL([VISA BUS DR CARD NQ RFND T/FEE],0)+ISNULL([VISA PRCH NQ REF TX(EX BUS DR)],0)+ISNULL([VISA BUS DR CARD NQ SRV CHG],0)+ISNULL([MC NQ SERVICE CHARGE (PREM)],0)+ISNULL([MC NON-QUAL REFUNDS (PREM)],0)+ISNULL([MC CHIP NQ SRV CHRG (PREM)],0)+ISNULL([MONTHLY INTERNET ACCESS FEE],0)+ISNULL([VAT INT MTH ACCESS FEE 17.5%],0)+ISNULL([TERMINAL RENTAL FEE],0)+ISNULL([VAT TERMINAL RENTAL FEE 17.5%],0)+ISNULL([VIRTUAL TERM TRANSACTION FEE],0)+ISNULL([VAT VIRT TERM TRAN FEE 15.0%],0)+ISNULL([MIN VIRTUAL TERMINAL TRAN FEE],0)+ISNULL([VAT MIN VIR TERM TRAN FEE15.0%],0)+ISNULL([VISA NQSERVICE CHG (CHCD)],0)+ISNULL([VISA NON.QUALREFUNDS(CHCD)],0)+ISNULL([VISA CHIP NQ SERVICE CHG(CHCD)],0)+ISNULL([VISA NON-QUAL SRV CHG EX.CHCD],0)+ISNULL([VISA NON-QUAL REFUNDS EX.CHCD],0)+ISNULL([VISA CHIP NQ SRV CHG EX.CHCD],0)+ISNULL([MC NQ SERVICE CHARGE EX. PREM],0)+ISNULL([MC NON-QUAL REFUNDS EX. PREM],0)+ISNULL([MC CHIP NQ SRV CHRG EX. PREM],0)+ISNULL([MC CHIP NQ REFUNDS EX. PREM],0)+ISNULL([VISA SERVICE CHARGE],0)+ISNULL([VISA REFUNDS],0)+ISNULL([VISA DEBIT SERVICE CHARGE],0)+ISNULL([VISA DEBIT REFUNDS],0)+ISNULL([VISA ELECTRON DR SERV CHARGE],0)+ISNULL([VISA ELECTRON DR REFUNDS],0)+ISNULL([MAESTRO SERVICE CHARGE],0)+ISNULL([MAESTRO REFUNDS],0)+ISNULL([SOLO SERVICE CHARGE],0)+ISNULL([SOLO REFUNDS],0)+ISNULL([VISA CHIP SERVICE CHARGE],0)+ISNULL([VISA CHIP REFUNDS],0)+ISNULL([VISA DEBIT CHIP SERVICE CHARGE],0)+ISNULL([MAESTRO CHIP SERVICE CHARGE],0)+ISNULL([SOLO CHIP SERVICE CHARGE],0)+--ISNULL([VISA PURCHASE CARD SERV CHRG],0)+--ISNULL([VISA PURCHASE CARD REFUNDS],0)+ISNULL([VISA COMMERCIAL CARD SERV CHRG],0)+ISNULL([VISA COMMERCIAL CARD REFUNDS],0)+ISNULL([VISA SALES TRANS FEE],0)+ISNULL([VISA REFUNDS TRANS FEE],0)+ISNULL([VISA DEBIT SALES TRANS FEE],0)+ISNULL([VISA DEBIT REFUNDS TRANS FEE],0)+ISNULL([VISA ELECTRON DR SALE TRAN FEE],0)+ISNULL([VISA ELECTRON DR RFD TRANS FEE],0)+ISNULL([MAESTRO SALES TRANS FEE],0)+ISNULL([MAESTRO REFUNDS TRANS FEE],0)+ISNULL([SOLO SALES TRANS FEE],0)+ISNULL([SOLO REFUNDS TRANS FEE],0)+ISNULL([VISA CHIP SALES TRANS FEE],0)+ISNULL([VISA CHIP REFUNDS TRANS FEE],0)+ISNULL([VISA DR CHIP SALE TRANS FEE],0)+ISNULL([VISA DR CHIP REFUNDS TRANS FEE],0)+ISNULL([VISA COMM CARD RFND T/FEE],0)+ISNULL([MAESTRO CHIP SALES TRANS FEE],0)+ISNULL([MAESTRO CHIP REFUNDS TRANS FEE],0)+ISNULL([SOLO CHIP SALES TRANS FEE],0)+ISNULL([SOLO CHIP REFUNDS TRANS FEE],0)+ISNULL([VISA PURCHASE CARD SALE T/FEE],0)+ISNULL([VISA PURCHASE CARD RFND T/FEE],0)+ISNULL([INTL MSTRO RFND TRN CNT-ED],0)+ISNULL([MASTERCARD SERVICE CHARGE],0)+ISNULL([MASTERCARD REFUNDS],0)+ISNULL([MASTERCARD CHIP SERVICE CHARGE],0)+ISNULL([MASTERCARD CHIP REFUNDS],0)+ISNULL([MC PURCHASE CARD SERV CHRG],0)+ISNULL([MC PURCHASE CARD REFUNDS],0)+ISNULL([MASTERCARD SALES TRANS FEE],0)+ISNULL([MASTERCARD REFUNDS TRANS FEE],0)+ISNULL([MASTERCARD CHIP SALE TRANS FEE],0)+ISNULL([MASTERCARD CHIP RFND TRANS FEE],0)+ISNULL([MC PURCHASE CARD SALE T/FEE],0)+ISNULL([MC PURCHASE CARD RFND T/FEE],0)+ISNULL([MC COMMERCIAL CARD RFND T/FEE],0)+ISNULL([MC COMMERCIAL CARD SERV CHRG],0)+ISNULL([MC COMMERCIAL CARD REFUNDS],0)+ISNULL([VISA PRCH SRV CHG(EX BUS DR)],0)+ISNULL([VISA PRCH REFUND (EX BUS DR)],0)+ISNULL([VISA BUS DR CARD SERV CHRG],0)+ISNULL([VISA SERVICE CHARGE EX.CHCD],0)+ISNULL([VISA REFUNDS EX.CHCD],0)+ISNULL([VISA CHIP SERVICE CHG EX.CHCD],0)+ISNULL([VISA CHIP REFUNDS EX.CHCD],0)+ISNULL([VISA BUS DR CARD SALE T/FEE],0)+ISNULL([VISA BUS DR CARD RFND T/FEE],0)+ISNULL([VISA PRCH SLS T/FEE(EX BUS DR)],0)+ISNULL([VISA PRCH REF T/FEE(EX BUS DR)],0)+ISNULL([MC SERVICE CHARGE (PREM)],0)+ISNULL([MC REFUNDS (PREM)],0)+ISNULL([MC CHIP SERVICE CHARGE (PREM)],0)+ISNULL([MC CHIP REFUNDS (PREM)],0)+ISNULL([MC REFUNDS TRANS FEE (PREM)],0)+ISNULL([MC CHIP RFND TRANS FEE (PREM)],0)+ISNULL([VISA SERVICE CHARGE(CHCD)],0)+ISNULL([VISA SERVICE CHARGE(CHCD) RFND],0)+ISNULL([VISA CHIP SERVICE CHARGE(CHCD)],0)+ISNULL([VISA PREM RFND T/FEE],0)+ISNULL([VISA REFUNDS TRANS FEE EX.CHCD],0)+ISNULL([VISA CHIP REFUND T/FEE EX.CHCD],0)+ISNULL([MC SERVICE CHARGE EX. PREM],0)+ISNULL([MC REFUNDS EX. PREM],0)+ISNULL([MC CHIP SERVICE CHG EX. PREM],0)+ISNULL([MC CHIP REFUNDS EX. PREM],0)+ISNULL([MC REFUNDS TRANS FEE EX. PREM],0)+ISNULL([MC CHIP REF TRANS FEE EX.PREM],0)+ISNULL([VISA COMM REF T/FEE(EX BUS DR)],0)+ISNULL([VISA COMM SERV CHRG(EX BUS DR)],0)+ISNULL([VISA COMM REFUNDS (EX BUS DR)],0)+ISNULL([MINIMUM MONTHLY CHARGE],0)+ISNULL([BOARDING FEE],0)+ISNULL([Authorisation Wholesale Fee],0)+ISNULL([AMEX],0)+ISNULL([CANCELLATION FEE],0)+ISNULL([FUNDING ADJUSTMENT],0)+ISNULL([DEBIT CARD DEPOSITS],0)+ISNULL([INTRCHNG REJ RESOLVED TO MERCH],0)+ISNULL([REPRESENTED DD],0)+ISNULL([TERMINAL BILLING],0)+ISNULL([FDGL Clawback],0)+ISNULL([FDGL CONFIG FEE],0)+ISNULL([FDGL Commission],0)+ISNULL([HIGH RISK UNDERWRITING FEE],0)+ISNULL([MSC ADJUSTMENT],0)+ISNULL([CREDIT ADJUSTMENT],0)+ISNULL([EBT CREDIT ADJUSTMENT],0)+ISNULL([MAESTRO CHARGEBACK REVERSAL CR],0)+ISNULL([MAESTRO CHARGEBACK CREDIT],0)+ISNULL([MAESTRO CHARGEBACK DEBIT],0)+ISNULL([MINIMUM ANNUAL SHORT FALL],0)+ISNULL([PAYMENT 1],0)+ISNULL([RECLAIMED FUNDS],0)+ISNULL([PAYMENT 3],0)+ISNULL([CHARGEBACK SUSPENSE],0)+ISNULL([SUSPENSE CLEARANCE],0)+ISNULL([ADJUSTMENT],0)+ISNULL([MASTERCARD ADJUSTMENTS],0)+ISNULL([TBA],0)+ISNULL([LETTER FEE],0)+ISNULL([NON-PAYMENT OF DIRECT DEBIT],0)+ISNULL([EXCESSIVE CHARGEBACK FEE],0)+ISNULL([OPERATIONAL GUIDANCE FEE],0)+ISNULL([COLLECTIONS FEE],0)+ISNULL([MANAGEMENT FEE],0)+ISNULL([TERMINATION FEE],0)+ISNULL([VAT TERMINAL FEE ADJUSTMENT],0)+ISNULL([TERMINAL FEE ADJUSTMENT],0)+ISNULL([MAESTRO ADJ. CUSTOMER INQUIRY],0)+ISNULL([LEGAL ENTITY ADMIN FEE],0)+ISNULL([CARD NOT PRESENT ADMIN FEE],0)+ISNULL([ADJUSTMENT],0)+ISNULL([MISC ADJUSTMENT],0)+ISNULL([PENDING INVESTIGATION],0)+ISNULL([MONTHLY RESIDENCE FEE],0)+ISNULL([Refund Wholesale fee],0) as 'Grand Total'FROM(SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description,SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales]FROM Dim_Fee_Codes INNER JOINFact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_NumberWHERE FDMSAccountNo in (select FDMSAccountNo from #LBG)GROUP BY Dim_Fee_Codes.Description,Fact_Fee_History_2.FDMSAccountNo) as pPIVOT (MAX([sales]) FOR Description IN ([REFUND TXN CHARGE],[AUTHORISATION REQUEST],[POLLING FEE],[CHARGEBACK FEE],[ANNUAL MEMBERSHIP FEE],[MMV MONTHLY ACCESS FEE],[CASHBACK FEE],[TERMINAL FEE],[MMV MONTHLY FEE],[MONTHLY STATEMENT FEE],[FDMS LINK MONTHLY FEE],[POS PARTNER],[SOFTWARE UPGRADE],[MC AUTO BILLING UPD MTH FEE],[MONTHLY CLIENTLINE FEE],[3-TIER MONTHLY SERVICE CHARGE],[MONTHLY MAINTENANCE FEE],[JOINING FEE],[PCI DSS MANAGEMENT FEE],[PCI DSS MANAGEMENT PM],[PCI DSS INACTIVITY CHARGE],[PCI DSS NON COMPLIANCE],[MC DEBIT INTERCHANGE PLUS],[MC DEBIT IC PLUS SALES],[MC DEBIT CHIP INTERCHANGE PLUS],[MC DEBIT CHIP IC PLUS SALES],[MAESTRO SALES IC FEE],[MAESTRO CHIP SALES IC FEE],[MAESTRO INTERCHANGE FEE],[MAESTRO CHIP INTERCHANGE FEE],[SOLO INTERCHANGE FEE],[SOLO CHIP INTERCHANGE FEE],[SOLO SALES IC FEE],[SOLO CHIP SALES IC FEE],[VISA SALES IC FEE],[VISA ELECTRON SALES IC FEE],[VISA CHIP SALES IC FEE],[VISA PURCH CARD SALES IC FEE],[VISA DEBIT SALES IC FEE],[VISA DEBIT CHIP SALES IC FEE],[VISA INTERCHANGE FEE],[VISA ELECTRON INTERCHANGE FEE],[VISA CHIP INTERCHANGE FEE],[VISA PURCH CARD INTRCHG FEE],[VISA DEBIT INTERCHANGE FEE],[VISA DEBIT CHIP INTERCHG FEE],[MASTERCARD SALES IC FEE],[MASTERCARD INTERCHANGE FEE],[MC INTERCHANGE],[MC CHIP INTERCHANGE FEE],[MC PURCH CARD INTERCHANGE FEE],[MC CHIP SALES IC FEE],[MC PURCH CARD SALES IC FEE],[DINERS INTERCHANGE FEE],[M/C+MAESTRO EU ACCEPTANCE FEE],[M/C INT. RETAIL ACCEPTANCE FEE],[MAESTRO INT. ACCEPTANCE FEE],[MC DEBIT SERVICE CHARGE],[MC DEBIT REFUNDS],[MC DEBIT SALES TRANSACTION FEE],[MC DEBIT REFUND TRANSACTION],[MC DEBIT NQ SERVICE CHARGE],[MC DEBIT NQ REFUNDS],[MC DEBIT NQ SALES TRANS FEE],[MC DEBIT NQ REFUND TRANSACTION],[MC DEBIT CHIP SERVICE CHARGE],[MC DEBIT CHIP SALES TRANS FEE],[MC DEBIT CHIP REFUND TRANS FEE],[MC DEBIT CHIP NQ SERVICE CHRG],[MC DBT CHP NQ SALES TRANS],[VISA NON-QUAL SALES TRANS FEE],[VISA NON-QUAL REFUNDS TRAN FEE],[VISA DEBIT NQ SALES TRANS FEE],[VISA DEBIT NQ REFUND TRANS FEE],[VISA ELECT DR NQ SALE TRAN FEE],[VISA ELECT DR NQ RFD TRANS FEE],[MAESTRO NON-QUAL SLS TRANS FEE],[SOLO NON-QUAL SALES TRANS FEE],[VISA CHIP NQ SALES TRANS FEE],[VISA CHIP NQ REFUNDS TRANS FEE],[VISA DR CHIP NQ SALES TRAN FEE],[VISA DR CHIP NQ RFND TRANS FEE],[MAESTRO CHIP NQ SALES TRANS FE],[SOLO CHIP NQ SALES TRANS FEE],[VISA PURCH CARD NQ RFND T/FEE],[VISA NON-QUAL SERVICE CHARGE],[VISA NON-QUAL REFUNDS],[VISA NQ DEBIT SERVICE CHARGE],[VISA NON-QUAL DEBIT REFUNDS],[VISA ELECTRON NQ DR SERV CHG],[VISA ELECTRON NQ DR REFUNDS],[VISA CHIP NQ SERVICE CHARGE],[VISA CHIP NON-QUAL REFUNDS],[VISA DEBIT CHIP NQ SERVICE CHG],[MAESTRO NON-QUAL SERVICE CHG],[INTL MSTRO NQ SERVICE CHARGE],[VISA PURCHASE CARD NQ SRV CHG],[MASTERCARD NQ SERVICE CHARGE],[MASTERCARD NON-QUAL REFUNDS],[MASTERCARD CHIP NQ SRV CHRG],[MASTERCARD CHIP NQ REFUNDS],[MC PURCHASE CARD NQ SRV CHRG],[MASTERCARD NQ SALES TRANS FEE],[MASTERCARD NQ RFND TRANS FEE],[MASTERCARD CHIP NQ RFND T/FEE],[VISA PRCH NQ SRVCHG(EX BUS DR)],[DINERS SALES TRANS FEE],[VISA BUS DR CARD NQ SALE T/FEE],[VISA BUS DR CARD NQ RFND T/FEE],[VISA PRCH NQ REF TX(EX BUS DR)],[VISA BUS DR CARD NQ SRV CHG],[MC NQ SERVICE CHARGE (PREM)],[MC NON-QUAL REFUNDS (PREM)],[MC CHIP NQ SRV CHRG (PREM)],[MONTHLY INTERNET ACCESS FEE],[VAT INT MTH ACCESS FEE 17.5%],[TERMINAL RENTAL FEE],[VAT TERMINAL RENTAL FEE 17.5%],[VIRTUAL TERM TRANSACTION FEE],[VAT VIRT TERM TRAN FEE 15.0%],[MIN VIRTUAL TERMINAL TRAN FEE],[VAT MIN VIR TERM TRAN FEE15.0%],[VISA NQSERVICE CHG (CHCD)],[VISA NON.QUALREFUNDS(CHCD)],[VISA CHIP NQ SERVICE CHG(CHCD)],[VISA NON-QUAL SRV CHG EX.CHCD],[VISA NON-QUAL REFUNDS EX.CHCD],[VISA CHIP NQ SRV CHG EX.CHCD],[MC NQ SERVICE CHARGE EX. PREM],[MC NON-QUAL REFUNDS EX. PREM],[MC CHIP NQ SRV CHRG EX. PREM],[MC CHIP NQ REFUNDS EX. PREM],[VISA SERVICE CHARGE],[VISA REFUNDS],[VISA DEBIT SERVICE CHARGE],[VISA DEBIT REFUNDS],[VISA ELECTRON DR SERV CHARGE],[VISA ELECTRON DR REFUNDS],[MAESTRO SERVICE CHARGE],[MAESTRO REFUNDS],[SOLO SERVICE CHARGE],[SOLO REFUNDS],[VISA CHIP SERVICE CHARGE],[VISA CHIP REFUNDS],[VISA DEBIT CHIP SERVICE CHARGE],[MAESTRO CHIP SERVICE CHARGE],[SOLO CHIP SERVICE CHARGE],[VISA PURCHASE CARD SERV CHRG[VISA PURCHASE CARD REFUNDS],[VISA COMMERCIAL CARD SERV CHRG],[VISA COMMERCIAL CARD REFUNDS],[VISA SALES TRANS FEE],[VISA REFUNDS TRANS FEE],[VISA DEBIT SALES TRANS FEE],[VISA DEBIT REFUNDS TRANS FEE],[VISA ELECTRON DR SALE TRAN FEE],[VISA ELECTRON DR RFD TRANS FEE],[MAESTRO SALES TRANS FEE],[MAESTRO REFUNDS TRANS FEE],[SOLO SALES TRANS FEE],[SOLO REFUNDS TRANS FEE],[VISA CHIP SALES TRANS FEE],[VISA CHIP REFUNDS TRANS FEE],[VISA DR CHIP SALE TRANS FEE],[VISA DR CHIP REFUNDS TRANS FEE],[VISA COMM CARD RFND T/FEE],[MAESTRO CHIP SALES TRANS FEE],[MAESTRO CHIP REFUNDS TRANS FEE],[SOLO CHIP SALES TRANS FEE],[SOLO CHIP REFUNDS TRANS FEE],[VISA PURCHASE CARD SALE T/FEE],[VISA PURCHASE CARD RFND T/FEE],[INTL MSTRO RFND TRN CNT-ED],[MASTERCARD SERVICE CHARGE],[MASTERCARD REFUNDS],[MASTERCARD CHIP SERVICE CHARGE],[MASTERCARD CHIP REFUNDS],[MC PURCHASE CARD SERV CHRG],[MC PURCHASE CARD REFUNDS],[MASTERCARD SALES TRANS FEE],[MASTERCARD REFUNDS TRANS FEE],[MASTERCARD CHIP SALE TRANS FEE],[MASTERCARD CHIP RFND TRANS FEE],[MC PURCHASE CARD SALE T/FEE],[MC PURCHASE CARD RFND T/FEE],[MC COMMERCIAL CARD RFND T/FEE],[MC COMMERCIAL CARD SERV CHRG],[MC COMMERCIAL CARD REFUNDS],[VISA PRCH SRV CHG(EX BUS DR)],[VISA PRCH REFUND (EX BUS DR)],[VISA BUS DR CARD SERV CHRG],[VISA SERVICE CHARGE EX.CHCD],[VISA REFUNDS EX.CHCD],[VISA CHIP SERVICE CHG EX.CHCD],[VISA CHIP REFUNDS EX.CHCD],[VISA BUS DR CARD SALE T/FEE],[VISA BUS DR CARD RFND T/FEE],[VISA PRCH SLS T/FEE(EX BUS DR)],[VISA PRCH REF T/FEE(EX BUS DR)],[MC SERVICE CHARGE (PREM)],[MC REFUNDS (PREM)],[MC CHIP SERVICE CHARGE (PREM)],[MC CHIP REFUNDS (PREM)],[MC REFUNDS TRANS FEE (PREM)],[MC CHIP RFND TRANS FEE (PREM)],[VISA SERVICE CHARGE(CHCD)],[VISA SERVICE CHARGE(CHCD) RFND],[VISA CHIP SERVICE CHARGE(CHCD)],[VISA PREM RFND T/FEE],[VISA REFUNDS TRANS FEE EX.CHCD],[VISA CHIP REFUND T/FEE EX.CHCD],[MC SERVICE CHARGE EX. PREM],[MC REFUNDS EX. PREM],[MC CHIP SERVICE CHG EX. PREM],[MC CHIP REFUNDS EX. PREM],[MC REFUNDS TRANS FEE EX. PREM],[MC CHIP REF TRANS FEE EX.PREM],[VISA COMM REF T/FEE(EX BUS DR)],[VISA COMM SERV CHRG(EX BUS DR)],[VISA COMM REFUNDS (EX BUS DR)],[MINIMUM MONTHLY CHARGE],[BOARDING FEE],[Authorisation Wholesale Fee],[AMEX],[CANCELLATION FEE],[FUNDING ADJUSTMENT],[DEBIT CARD DEPOSITS],[INTRCHNG REJ RESOLVED TO MERCH],[REPRESENTED DD],[TERMINAL BILLING],[FDGL Clawback],[FDGL CONFIG FEE],[FDGL Commission],[HIGH RISK UNDERWRITING FEE],[MSC ADJUSTMENT],[CREDIT ADJUSTMENT],[EBT CREDIT ADJUSTMENT],[MAESTRO CHARGEBACK REVERSAL CR],[MAESTRO CHARGEBACK CREDIT],[MAESTRO CHARGEBACK DEBIT],[MINIMUM ANNUAL SHORT FALL],[PAYMENT 1],[RECLAIMED FUNDS],[PAYMENT 3],[CHARGEBACK SUSPENSE],[SUSPENSE CLEARANCE],[ADJUSTMENT],[MASTERCARD ADJUSTMENTS],[TBA],[LETTER FEE],[NON-PAYMENT OF DIRECT DEBIT],[EXCESSIVE CHARGEBACK FEE],[OPERATIONAL GUIDANCE FEE],[COLLECTIONS FEE],[MANAGEMENT FEE],[TERMINATION FEE],[VAT TERMINAL FEE ADJUSTMENT],[TERMINAL FEE ADJUSTMENT],[MAESTRO ADJ. CUSTOMER INQUIRY],[LEGAL ENTITY ADMIN FEE],[CARD NOT PRESENT ADMIN FEE],[MISC ADJUSTMENT],[PENDING INVESTIGATION],[MONTHLY RESIDENCE FEE],[Refund Wholesale fee]) ) as pvt |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-22 : 10:17:58
|
If I understood you correctly, you want to eliminate any column in the final pivoted results if all the rows contain null values for that specific column.The only options two options that I can think of are to either 1. pre-process your query to eliminate any columns that have all null values and then use a dynamic query to do the pivot. The dynamic query should only have the columns you want in the pivot list.2. Keep the current query and post-process it to remove any columns where all the pivoted values are null. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 13:58:21
|
hi sunitabeckyes i want to eliminate any column in the final pivoted results if all the rows contain null values in that specific columnHow can i do option 2. Keep the current query and post-process it to remove any columns where all the pivoted values are null. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-23 : 02:52:45
|
Hi mansod,Run this below script once....create table temp( date datetime, category varchar(3), amount money)GOinsert into temp values ('1/1/2012', 'ABC', 1000.00)insert into temp values ('2/1/2012', 'DEF', NULL)insert into temp values ('2/1/2012', 'GHI', 800.00)insert into temp values ('4/10/2012', 'DEF', NULL)insert into temp values ('4/1/2012', 'ABC', 300)GOSELECT date, [ABC], [DEF], [GHI]FROM (SELECT date, category, sum(amount) Amt from temp GROUP BY date, category )as p PIVOT ( SUM(amt) FOR category IN ([abc], [def], [ghi])) as pvtGO DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) FROM (SELECT category from temp GROUP BY date, category HAVING sum(amount) IS NOT NULL) c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT date, ' + @cols + ' from ( SELECT date, category, sum(amount) Amt from temp GROUP BY date, category HAVING sum(amount) IS NOT NULL ) x pivot ( max(amt) for category in (' + @cols + ') ) p 'exec sp_executesql @queryGODROP TABLE tempGO ------------------------------------------------------------------Now this is modified code for your requirementDECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Description]) FROM (SELECT Dim_Fee_Codes.Description FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query = 'SELECT FDMSAccountNo, ' + @cols + ' from (SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description, SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales] FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) x pivot ( MAX([sales] FOR Description IN (' + @cols + ') ) p 'SELECT @queryexec sp_executesql @queryGO --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 05:27:09
|
Hi Bandi when i run the following above i get the following error msg Msg 262, Level 14, State 1, Line 1CREATE TABLE permission denied in database 'FDMS'.Msg 208, Level 16, State 1, Line 1Invalid object name 'temp'.Msg 208, Level 16, State 1, Line 1Invalid object name 'temp'.Msg 208, Level 16, State 1, Line 5Invalid object name 'temp'.Msg 3701, Level 11, State 5, Line 1Cannot drop the table 'temp', because it does not exist or you do not have permission.Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'is'.Is there any alternative way ? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 05:42:29
|
oh that is your access rights issue.. ok then declare @temp table( date datetime, category varchar(3), amount money)then replace temp with @temp--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 05:46:08
|
Bandi sorry to seem thick But how is the code, meant to look ? with the new modification ? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 06:08:33
|
Then run this one only. No need of any modificationDECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Description]) FROM (SELECT Dim_Fee_Codes.Description FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query = 'SELECT FDMSAccountNo, ' + @cols + ' from (SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description, SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales] FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) x pivot ( MAX([sales] FOR Description IN (' + @cols + ') ) p '--SELECT @queryexec sp_executesql @queryGO --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 06:13:04
|
HI Bandi This is the following query as per your dicussion, but i get the following error msg Msg 156, Level 15, State 1, Line 11Incorrect syntax near the keyword 'FOR'.select ParentID,FDMSAccountNo into #LBG from FDMS.dbo.Dim_Outlet where ParentID in ('878180410887','878231937888','878591844880','878206428889','878530843886','878228175880','878074500884','878050500882','878232584887','878054000889','878163140881','878229307888','878033800888','878191050888','878202862883','878220050883','878110100889','878590795885','878061000880','878706015889','878055800881','878190240886','878160010889','878700673881','878216109883','878234145885','878226616885','878230974882','878195870885','878163460883','878202753884','878701632886','878179830889','878195840888','878201390886','878027373884','878197770885')DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Description]) FROM (SELECT Dim_Fee_Codes.Description FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query = 'SELECT FDMSAccountNo, ' + @cols + ' from (SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description, SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales] FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) x pivot ( MAX([sales] FOR Description IN (' + @cols + ') ) p '--SELECT @queryexec sp_executesql @queryGO |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 06:28:05
|
Oh u r getting error at FOR XML PATH...Which version of SQL SERVER u r using? (that means sql server 2005 or 2008)did you rectify error?--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 06:40:09
|
sql server 2008 r2 |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 06:48:18
|
Hi Bandi I dont understand what you mean |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 06:51:56
|
Execute this and then send outputDECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Description]) FROM (SELECT Dim_Fee_Codes.DescriptionFROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_NumberWHERE FDMSAccountNo in (select FDMSAccountNo from #LBG)GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNoHAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL) cFOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')set @query = 'SELECT FDMSAccountNo, ' + @cols + ' from (SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description, SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales]FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_NumberWHERE FDMSAccountNo in (select FDMSAccountNo from #LBG)GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNoHAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL) xpivot (MAX([sales] FOR Description IN (' + @cols + ')) p 'SELECT @query --Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 06:56:08
|
hey its a typo.........pivot (MAX([sales]) FOR Description IN (' + @cols + ')) p '--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 07:00:18
|
Hi Bandi When i use your following code, i get the following (Please follow link for outcome)http://s17.postimage.org/inhkon3lb/outcome.jpg |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 07:10:07
|
See previous reply posted at 11/26/2012 : 06:56:08Let me know once u got result--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 07:15:21
|
HI bandi I still get the same error, please find attached, along with query http://s13.postimage.org/iwurpdnzr/Newoutcome.jpg |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 07:33:27
|
I am unable to see the output in that image. Please copy only output value by using following process (simply select resultset and then CTRL+SHIFT+C)you can right click on the resultset and click on Copy Header. This will copy the headers along with the resultset. Additionally, you can use the shortcut key CTRL+SHIFT+C for coping column headers along with the resultset.And also tell me which error you are getting....--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-26 : 07:41:19
|
Hi i am getting the following, SELECT FDMSAccountNo, [VISA COMM CARD RFND T/FEE],[MC PURCHASE CARD SERV CHRG],[MAESTRO CHIP NQ SALES TRANS FE],[MC DEBIT CHIP NQ SERVICE CHRG],[MC DEBIT NQ SERVICE CHARGE],[MAESTRO CHARGEBACK CREDIT],[VISA PRCH SLS T/FEE(EX BUS DR)],[VISA PURCHASE CARD SALE T/FEE],[MASTERCARD REFUNDS],[VISA COMM REFUNDS (EX BUS DR)],[VISA CHIP SERVICE CHG EX.CHCD],[MC COMMERCIAL CARD SERV CHRG],[TERMINAL RENTAL FEE],[VISA CHIP NQ SRV CHG EX.CHCD],[MC COMMERCIAL CARD REFUNDS],[MC NON-QUAL REFUNDS (PREM)],[MISC ADJUSTMENT],[MC PURCH CARD SALES IC FEE],[MC DEBIT IC PLUS SALES],[MAESTRO SALES TRANS FEE],[TERMINAL FEE ADJUSTMENT],[MONTHLY CLIENTLINE FEE],[REPRESENTED DD],[VISA COMM REF T/FEE(EX BUS DR)],[MC REFUNDS TRANS FEE EX. PREM],[SOLO NON-QUAL SALES TRANS FEE],[VISA BUS DR CARD SALE T/FEE],[PCI DSS NON COMPLIANCE],[MASTERCARD NQ SERVICE CHARGE],[VISA CHIP REFUNDS],[VISA DEBIT SALES TRANS FEE],[VISA CHIP SALES IC FEE],[VISA SALES TRANS FEE],[PENDING INVESTIGATION],[MAESTRO NON-QUAL SLS TRANS FEE],[PCI DSS MANAGEMENT PM],[MASTERCARD CHIP SALE TRANS FEE],[MC DEBIT SALES TRANSACTION FEE],[MC REFUNDS EX. PREM],[MAESTRO REFUNDS TRANS FEE],[REFUND TXN CHARGE],[M/C+MAESTRO EU ACCEPTANCE FEE],[MC CHIP REF TRANS FEE EX.PREM],[MASTERCARD CHIP RFND TRANS FEE],[MC CHIP NQ SRV CHRG EX. PREM],[MC REFUNDS (PREM)],[MC CHIP SERVICE CHARGE (PREM)],[VISA NQSERVICE CHG (CHCD)],[VISA CHIP REFUNDS TRANS FEE],[VISA CHIP SERVICE CHARGE(CHCD)],[CASHBACK FEE],[VISA DR CHIP REFUNDS TRANS FEE],[MMV MONTHLY FEE],[VISA CHIP REFUND T/FEE EX.CHCD],[VISA DEBIT SALES IC FEE],[MONTHLY MAINTENANCE FEE],[MC NON-QUAL REFUNDS EX. PREM],[SOLO CHIP NQ SALES TRANS FEE],[VAT TERMINAL RENTAL FEE 17.5%],[VISA SERVICE CHARGE EX.CHCD],[MC CHIP NQ SRV CHRG (PREM)],[CHARGEBACK SUSPENSE],[MC DEBIT SERVICE CHARGE],[MC CHIP REFUNDS (PREM)],[VISA COMMERCIAL CARD REFUNDS],[SOLO CHIP SALES TRANS FEE],[MC PURCHASE CARD SALE T/FEE],[VISA COMMERCIAL CARD SERV CHRG],[SOLO SALES IC FEE],[MAESTRO CHIP SALES TRANS FEE],[VISA CHIP REFUNDS EX.CHCD],[VISA CHIP SALES TRANS FEE],[MASTERCARD SALES IC FEE],[MC DEBIT NQ SALES TRANS FEE],[VISA DEBIT CHIP SALES IC FEE],[VISA CHIP NQ SERVICE CHG(CHCD)],[VISA BUS DR CARD NQ SALE T/FEE],[MASTERCARD CHIP REFUNDS],[TERMINATION FEE],[MC REFUNDS TRANS FEE (PREM)],[POLLING FEE],[SUSPENSE CLEARANCE],[MC SERVICE CHARGE (PREM)],[VISA PRCH SRV CHG(EX BUS DR)],[MAESTRO INT. ACCEPTANCE FEE],[MC CHIP RFND TRANS FEE (PREM)],[SOLO CHIP SALES IC FEE],[VISA ELECT DR NQ SALE TRAN FEE],[MC CHIP SERVICE CHG EX. PREM],[MC DEBIT CHIP IC PLUS SALES],[VISA SERVICE CHARGE(CHCD)],[MAESTRO CHIP SERVICE CHARGE],[MC CHIP SALES IC FEE],[PCI DSS INACTIVITY CHARGE],[MC DEBIT CHIP SALES TRANS FEE],[MASTERCARD REFUNDS TRANS FEE],[MASTERCARD SERVICE CHARGE],[VISA NON-QUAL SRV CHG EX.CHCD],[VISA REFUNDS TRANS FEE],[MASTERCARD CHIP SERVICE CHARGE],[VISA BUS DR CARD RFND T/FEE],[VISA DEBIT REFUNDS TRANS FEE],[MC DEBIT CHIP SERVICE CHARGE],[PCI DSS MANAGEMENT FEE],[VISA ELECTRON NQ DR SERV CHG],[VISA REFUNDS],[VISA REFUNDS EX.CHCD],[MAESTRO CHIP REFUNDS TRANS FEE],[VISA CHIP NQ SERVICE CHARGE],[VISA SALES IC FEE],[VISA DR CHIP SALE TRANS FEE],[MINIMUM MONTHLY CHARGE],[VISA ELECTRON DR RFD TRANS FEE],[VISA ELECTRON SALES IC FEE],[VISA SERVICE CHARGE],[MAESTRO CHARGEBACK REVERSAL CR],[MSC ADJUSTMENT],[MASTERCARD ADJUSTMENTS],[MC PURCHASE CARD RFND T/FEE],[VAT TERMINAL FEE ADJUSTMENT],[AUTHORISATION REQUEST],[VISA DR CHIP NQ SALES TRAN FEE],[MC DBT CHP NQ SALES TRANS],[SOLO SALES TRANS FEE],[MASTERCARD CHIP NQ SRV CHRG],[LETTER FEE],[MC NQ SERVICE CHARGE (PREM)],[JOINING FEE],[VISA ELECTRON DR SERV CHARGE],[M/C INT. RETAIL ACCEPTANCE FEE],[INTL MSTRO NQ SERVICE CHARGE],[VISA ELECTRON DR SALE TRAN FEE],[VISA CHIP SERVICE CHARGE],[SOLO REFUNDS TRANS FEE],[SOLO CHIP SERVICE CHARGE],[VISA REFUNDS TRANS FEE EX.CHCD],[MAESTRO CHARGEBACK DEBIT],[MAESTRO CHIP SALES IC FEE],[MC NQ SERVICE CHARGE EX. PREM],[SOLO CHIP REFUNDS TRANS FEE],[MC CHIP REFUNDS EX. PREM],[VISA DEBIT NQ SALES TRANS FEE],[MAESTRO SALES IC FEE],[CHARGEBACK FEE],[MC COMMERCIAL CARD RFND T/FEE],[VISA NON-QUAL SERVICE CHARGE],[VISA COMM SERV CHRG(EX BUS DR)],[VISA PURCH CARD SALES IC FEE],[MC SERVICE CHARGE EX. PREM],[MASTERCARD SALES TRANS FEE] from (SELECT Fact_Fee_History_2.FDMSAccountNo, Dim_Fee_Codes.Description, SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales] FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG) GROUP BY Dim_Fee_Codes.Description, Fact_Fee_History_2.FDMSAccountNo HAVING SUM(Fact_Fee_History_2.Retail_amount_R12) IS NOT NULL ) x pivot ( MAX([sales]) FOR Description IN ([VISA COMM CARD RFND T/FEE],[MC PURCHASE CARD SERV CHRG],[MAESTRO CHIP NQ SALES TRANS FE],[MC DEBIT CHIP NQ SERVICE CHRG],[MC DEBIT NQ SERVICE CHARGE],[MAESTRO CHARGEBACK CREDIT],[VISA PRCH SLS T/FEE(EX BUS DR)],[VISA PURCHASE CARD SALE T/FEE],[MASTERCARD REFUNDS],[VISA COMM REFUNDS (EX BUS DR)],[VISA CHIP SERVICE CHG EX.CHCD],[MC COMMERCIAL CARD SERV CHRG],[TERMINAL RENTAL FEE],[VISA CHIP NQ SRV CHG EX.CHCD],[MC COMMERCIAL CARD REFUNDS],[MC NON-QUAL REFUNDS (PREM)],[MISC ADJUSTMENT],[MC PURCH CARD SALES IC FEE],[MC DEBIT IC PLUS SALES],[MAESTRO SALES TRANS FEE],[TERMINAL FEE ADJUSTMENT],[MONTHLY CLIENTLINE FEE],[REPRESENTED DD],[VISA COMM REF T/FEE(EX BUS DR)],[MC REFUNDS TRANS FEE EX. PREM],[SOLO NON-QUAL SALES TRANS FEE],[VISA BUS DR CARD SALE T/FEE],[PCI DSS NON COMPLIANCE],[MASTERCARD NQ SERVICE CHARGE],[VISA CHIP REFUNDS],[VISA DEBIT SALES TRANS FEE],[VISA CHIP SALES IC FEE],[VISA SALES TRANS FEE],[PENDING INVESTIGATION],[MAESTRO NON-QUAL SLS TRANS FEE],[PCI DSS MANAGEMENT PM],[MASTERCARD CHIP SALE TRANS FEE],[MC DEBIT SALES TRANSACTION FEE],[MC REFUNDS EX. PREM],[MAESTRO REFUNDS TRANS FEE],[REFUND TXN CHARGE],[M/C+MAESTRO EU ACCEPTANCE FEE],[MC CHIP REF TRANS FEE EX.PREM],[MASTERCARD CHIP RFND TRANS FEE],[MC CHIP NQ SRV CHRG EX. PREM],[MC REFUNDS (PREM)],[MC CHIP SERVICE CHARGE (PREM)],[VISA NQSERVICE CHG (CHCD)],[VISA CHIP REFUNDS TRANS FEE],[VISA CHIP SERVICE CHARGE(CHCD)],[CASHBACK FEE],[VISA DR CHIP REFUNDS TRANS FEE],[MMV MONTHLY FEE],[VISA CHIP REFUND T/FEE EX.CHCD],[VISA DEBIT SALES IC FEE],[MONTHLY MAINTENANCE FEE],[MC NON-QUAL REFUNDS EX. PREM],[SOLO CHIP NQ SALES TRANS FEE],[VAT TERMINAL RENTAL FEE 17.5%],[VISA SERVICE CHARGE EX.CHCD],[MC CHIP NQ SRV CHRG (PREM)],[CHARGEBACK SUSPENSE],[MC DEBIT SERVICE CHARGE],[MC CHIP REFUNDS (PREM)],[VISA COMMERCIAL CARD REFUNDS],[SOLO CHIP SALES TRANS FEE],[MC PURCHASE CARD SALE T/FEE],[VISA COMMERCIAL CARD SERV CHRG],[SOLO SALES IC FEE],[MAESTRO CHIP SALES TRANS FEE],[VISA CHIP REFUNDS EX.CHCD],[VISA CHIP SALES TRANS FEE],[MASTERCARD SALES IC FEE],[MC DEBIT NQ SALES TRANS FEE],[VISA DEBIT CHIP SALES IC FEE],[VISA CHIP NQ SERVICE CHG(CHCD)],[VISA BUS DR CARD NQ SALE T/FEE],[MASTERCARD CHIP REFUNDS],[TERMINATION FEE],[MC REFUNDS TRANS FEE (PREM)],[POLLING FEE],[SUSPENSE CLEARANCE],[MC SERVICE CHARGE (PREM)],[VISA PRCH SRV CHG(EX BUS DR)],[MAESTRO INT. ACCEPTANCE FEE],[MC CHIP RFND TRANS FEE (PREM)],[SOLO CHIP SALES IC FEE],[VISA ELECT DR NQ SALE TRAN FEE],[MC CHIP SERVICE CHG EX. PREM],[MC DEBIT CHIP IC PLUS SALES],[VISA SERVICE CHARGE(CHCD)],[MAESTRO CHIP SERVICE CHARGE],[MC CHIP SALES IC FEE],[PCI DSS INACTIVITY CHARGE],[MC DEBIT CHIP SALES TRANS FEE],[MASTERCARD REFUNDS TRANS FEE],[MASTERCARD SERVICE CHARGE],[VISA NON-QUAL SRV CHG EX.CHCD],[VISA REFUNDS TRANS FEE],[MASTERCARD CHIP SERVICE CHARGE],[VISA BUS DR CARD RFND T/FEE],[VISA DEBIT REFUNDS TRANS FEE],[MC DEBIT CHIP SERVICE CHARGE],[PCI DSS MANAGEMENT FEE],[VISA ELECTRON NQ DR SERV CHG],[VISA REFUNDS],[VISA REFUNDS EX.CHCD],[MAESTRO CHIP REFUNDS TRANS FEE],[VISA CHIP NQ SERVICE CHARGE],[VISA SALES IC FEE],[VISA DR CHIP SALE TRANS FEE],[MINIMUM MONTHLY CHARGE],[VISA ELECTRON DR RFD TRANS FEE],[VISA ELECTRON SALES IC FEE],[VISA SERVICE CHARGE],[MAESTRO CHARGEBACK REVERSAL CR],[MSC ADJUSTMENT],[MASTERCARD ADJUSTMENTS],[MC PURCHASE CARD RFND T/FEE],[VAT TERMINAL FEE ADJUSTMENT],[AUTHORISATION REQUEST],[VISA DR CHIP NQ SALES TRAN FEE],[MC DBT CHP NQ SALES TRANS],[SOLO SALES TRANS FEE],[MASTERCARD CHIP NQ SRV CHRG],[LETTER FEE],[MC NQ SERVICE CHARGE (PREM)],[JOINING FEE],[VISA ELECTRON DR SERV CHARGE],[M/C INT. RETAIL ACCEPTANCE FEE],[INTL MSTRO NQ SERVICE CHARGE],[VISA ELECTRON DR SALE TRAN FEE],[VISA CHIP SERVICE CHARGE],[SOLO REFUNDS TRANS FEE],[SOLO CHIP SERVICE CHARGE],[VISA REFUNDS TRANS FEE EX.CHCD],[MAESTRO CHARGEBACK DEBIT],[MAESTRO CHIP SALES IC FEE],[MC NQ SERVICE CHARGE EX. PREM],[SOLO CHIP REFUNDS TRANS FEE],[MC CHIP REFUNDS EX. PREM],[VISA DEBIT NQ SALES TRANS FEE],[MAESTRO SALES IC FEE],[CHARGEBACK FEE],[MC COMMERCIAL CARD RFND T/FEE],[VISA NON-QUAL SERVICE CHARGE],[VISA COMM SERV CHRG(EX BUS DR)],[VISA PURCH CARD SALES IC FEE],[MC SERVICE CHARGE EX. PREM],[MASTERCARD SALES TRANS FEE]) ) p |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 08:01:44
|
Hi, i have seen ur output.........i executed same query with sample data.... But no error for mewhich error you are getting? Show us. And also try to solve--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-26 : 08:19:42
|
By using this dynamic query you got minimized Select column-list to 151 instead of 500 columns.. Let me know which problem you are facing....In your post( at 11/26/2012 : 07:15:21), you forgot to execute that prepared string @queryi.e. EXEC sp_executesql @query statement--Chandu |
|
|
Previous Page&nsp;
Next Page
|
|
|