Skip to Content
0
Jun 21, 2021 at 06:09 AM

Need to change the querry from Vertical to Horizantal

54 Views

Hi experts

I need to change the attached query in sql to Hortizantal, which now appering as VERTCAL

Can any one help me to solve this by editing

Regards

Vinod

SELECT    
	     OVPM."DocEntry"
	    ,OVPM."DocNum"   AS  "Voucher No."
	    ,OVPM."DocDate"  AS  "Voucher Date"
		,OVPM."CardCode" AS  "Vendor Code"
		,OVPM."CardName" AS  "Vendor Name"		
		,CASE WHEN VPM2."InvType"=18 THEN OPCH."DocNum"
					  WHEN VPM2."InvType"=19 THEN ORPC."DocNum" END AS "Invoice No"					  
		,CASE WHEN VPM2."InvType"=18 THEN OPCH."NumAtCard"
					  WHEN VPM2."InvType"=19 THEN ORPC."NumAtCard" END AS "Vendor Invoice Reference"
		,CASE WHEN VPM2."InvType"=18 THEN OPCH."DocDate"
					  WHEN VPM2."InvType"=19 THEN ORPC."DocDate" END   AS "Vendor Invoice Date"
		,CASE WHEN VPM2."InvType"=18 THEN OPCH."DocTotal"
					  WHEN VPM2."InvType"=19 THEN ORPC."DocTotal" END  AS "Bill Amount"
		,CASE WHEN VPM2."InvType"<>'' OR VPM2."InvType"<>'NULL' THEN  
		(CASE WHEN VPM2."InvType"=19   THEN VPM2."SumApplied"
						WHEN VPM2."InvType"<>19 THEN VPM2."SumApplied"
						WHEN VPM4."AcctCode"<>'' OR VPM4."AcctCode"<>'NULL' THEN VPM4."SumApplied"
						ELSE OVPM."DocTotal" END) END AS "Line Total Payment Amt"
		,CASE WHEN VPM2."InvType"=18 THEN opch."DocTotal"
										WHEN VPM2."InvType"=19 THEN (-ORPC."DocTotal" ) END "Invoice Doc Total"
		,(OPCH."DocTotal"- vpm2."SumApplied") "Due Amt"
		 ,OVPM."DocTotal" AS "Outgoing payment Doc total"
		,CASE WHEN VPM2."InvType"=18 THEN 'A/P Invoice'
					  WHEN VPM2."InvType"=19 THEN 'Credit Note'
					  WHEN vpm2."InvType"=46 THEN 'Outgoing Payment'
				      WHEN VPM2."InvType"=23 THEN 'Incoming Payment' 
					  WHEN VPM2."InvType"=30 THEN 'Journal Entry'
					  WHEN VPM2."InvType"=-2 THEN 'Opening Balance' ELSE 'Payment on Account' END "Base Document Type"
		 ,VPM1."CheckNum" "Cheque No" 
		 --,CASE WHEN (ISNULL(OVPM."CashSum",0) <> 0 ) THEN 'Cash Transfer'
			--WHEN (ISNULL(OVPM."CheckSum",0) <> 0 ) THEN 'Cheque Transfer'
			--WHEN (ISNULL(OVPM."TrsfrSum",0) <> 0 ) THEN 'NEFT' 
			--WHEN OJDT."TransType" = '30' THEN 'JEs' 
			--ELSE ''
			-- END AS "Transaction Type"
		
                      
FROM         RAPID.ODSC  RIGHT OUTER JOIN
                      RAPID.OVPM  LEFT OUTER JOIN
                      RAPID.VPM4  ON RAPID.OVPM."DocEntry" = RAPID.VPM4."DocNum" LEFT OUTER JOIN
                      RAPID.VPM1  ON RAPID.OVPM."DocEntry" = RAPID.VPM1."DocNum" LEFT OUTER JOIN
                      RAPID.OACT  ON RAPID.OVPM."TrsfrAcct" = RAPID.OACT."AcctCode" ON RAPID.ODSC."BankCode" = RAPID.VPM1."BankCode" 
                      LEFT OUTER JOIN
                      RAPID.VPM2  ON RAPID.OVPM."DocEntry" = RAPID.VPM2."DocNum"
                      LEFT OUTER JOIN RAPID.OPCH  ON RAPID.OPCH."DocEntry"=RAPID.VPM2."DocEntry" AND RAPID.VPM2."InvType"=18
                      LEFT OUTER JOIN RAPID.ORPC  ON RAPID.ORPC."DocEntry"=RAPID.VPM2."DocEntry" AND RAPID.VPM2."InvType"=19
                      LEFT OUTER JOIN RAPID.OJDT  ON RAPID.OJDT."TransId"=RAPID.OPCH."TransId"  
                                          
                      LEFT OUTER JOIN RAPID.OJDT  X ON X."TransId"=RAPID.ORPC."TransId"
                      
                      
                      LEFT OUTER JOIN RAPID.OJDT  Y  ON Y."TransId"=RAPID.VPM2."DocEntry" AND RAPID.VPM2."InvType" IN (23,46,30)
                     
                     
                      LEFT JOIN RAPID.OCRD  ON RAPID.OCRD."CardCode"=RAPID.OVPM."CardCode"
--WHERE     OVPM.DocDate =(SELECT CONVERT(date, getdate()))
WHERE     OVPM."DocNum" = '728'
GROUP BY    OVPM."DocNum", OVPM."DocDate", OVPM."CardName",OVPM."CardCode",  OVPM."DocTotal", 
 VPM1."CheckNum",  OPCH."DocTotal",VPM2."SumApplied",OPCH."DocNum",OPCH."NumAtCard",ORPC."DocTotal"
,OJDT."Number",X."Number",Y."Number",ORPC."DocNum",ORPC."NumAtCard" ,OPCH."DocDate",ORPC."DocDate",
VPM2."InvType",VPM4."AcctCode",VPM4."SumApplied"
,VPM1."CheckNum",ovpm."DocEntry",OVPM."CashSum",OVPM."CheckSum",OVPM."TrsfrSum",OJDT."TransType"
order by
ovpm."DocEntry" asc