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
 Pivot Question
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/22/2012 :  06:25:14  Show Profile  Reply with Quote
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 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
) as p
PIVOT (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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/22/2012 :  10:17:58  Show Profile  Reply with Quote
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.
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/22/2012 :  13:58:21  Show Profile  Reply with Quote
hi sunitabeck
yes i want to eliminate any column in the final pivoted results if all the rows contain null values in that specific column


How can i do option 2. Keep the current query and post-process it to remove any columns where all the pivoted values are null.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/23/2012 :  02:52:45  Show Profile  Reply with Quote
Hi mansod,
Run this below script once....

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)
GO
insert 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)
GO
SELECT 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 pvt
GO
	
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 @query
GO
DROP TABLE temp
GO

------------------------------------------------------------------

Now this is modified code for your requirement

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 @query
exec sp_executesql @query
GO


--
Chandu

Edited by - bandi on 11/23/2012 03:29:29
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  05:27:09  Show Profile  Reply with Quote
Hi Bandi

when i run the following above i get the following error msg

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'FDMS'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'temp'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'temp'.
Msg 208, Level 16, State 1, Line 5
Invalid object name 'temp'.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'temp', because it does not exist or you do not have permission.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'is'.


Is there any alternative way ?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  05:42:29  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  05:46:08  Show Profile  Reply with Quote
Bandi

sorry to seem thick

But how is the code, meant to look ? with the new modification ?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  06:08:33  Show Profile  Reply with Quote
Then run this one only. No need of any modification

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 @query
exec sp_executesql @query
GO



--
Chandu

Edited by - bandi on 11/26/2012 06:09:57
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  06:13:04  Show Profile  Reply with Quote
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 11
Incorrect 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 @query
exec sp_executesql @query
GO



Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  06:28:05  Show Profile  Reply with Quote
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

Edited by - bandi on 11/26/2012 06:40:07
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  06:40:09  Show Profile  Reply with Quote
sql server 2008 r2
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  06:48:18  Show Profile  Reply with Quote
Hi Bandi

I dont understand what you mean
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  06:51:56  Show Profile  Reply with Quote
Execute this and then send output

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 @query


--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  06:56:08  Show Profile  Reply with Quote
hey its a typo.........


pivot
(
MAX([sales]) FOR Description IN (' + @cols + ')
) p '


--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  07:00:18  Show Profile  Reply with Quote
Hi Bandi

When i use your following code, i get the following
(Please follow link for outcome)
http://s17.postimage.org/inhkon3lb/outcome.jpg
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  07:10:07  Show Profile  Reply with Quote
See previous reply posted at 11/26/2012 : 06:56:08
Let me know once u got result

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  07:15:21  Show Profile  Reply with Quote
HI bandi

I still get the same error, please find attached, along with query
http://s13.postimage.org/iwurpdnzr/Newoutcome.jpg
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  07:33:27  Show Profile  Reply with Quote
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

Edited by - bandi on 11/26/2012 07:35:52
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/26/2012 :  07:41:19  Show Profile  Reply with Quote

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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  08:01:44  Show Profile  Reply with Quote
Hi, i have seen ur output.........
i executed same query with sample data.... But no error for me


which error you are getting? Show us. And also try to solve

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  08:19:42  Show Profile  Reply with Quote
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 @query
i.e.
EXEC sp_executesql @query statement

--
Chandu

Edited by - bandi on 11/26/2012 08:36:53
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.36 seconds. Powered By: Snitz Forums 2000