0

I have a SQL server DB for credit card processing system with 5 Tables

MerchantContract

PaymentKey   NVARCHAR(50)
ProcessingID INT

DealTrace

Status       NVARCHAR(250)
Tag          NVARCHAR(MAX)

DealSummmary

RefNumber    NVARCHAR(100)
DealAuthCode NVARCHAR(100)

Processing

Name         NVARCHAR(50)

And Deal with whole bunch of fields of which the most important one are

OriginalOrderId  NVARCHAR(50)
MerchantContract INT
Amount           INT

Currently I'm querying these tables with such query:

SELECT  d.id, 
        d.date, 
        d.ip, 
        d.panmask, 
        d.merchantorderid, 
        d.amount, 
        d.cardholder, 
        d.bankhumanname, 
        d.cardtypeid, 
        d.bankcountrycode, 
        d.usercountrycode, 
        mc.paymentkey as merchantname, 
        dt.status, 
        d.merchantcontract, 
        dt.tag, 
        d.originalorderid, 
        ds.refnumber, 
        ds.dealauthcode, 
        mc.processingid, 
        pc.Name as processing, 
        d.customparams 
FROM Deal as d 
LEFT JOIN MerchantContract as mc ON mc.Id = d.MerchantContract 
LEFT JOIN DealTrace as dt ON d.Id = dt.DealId 
AND dt.id = (SELECT MAX(id) FROM DealTrace WITH (nolock) WHERE DealId = d.id) 
LEFT JOIN DealSummary ds ON d.Id = ds.DealId 
AND ds.id = (SELECT MAX(id) FROM DealSummary WITH (nolock) WHERE DealId = d.id) 
LEFT JOIN Processing pc on mc.ProcessingId = pc.id WHERE (d.MerchantContract IN ('13')) 
ORDER BY ID desc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

However for each MerchantContract there might be several rows with OriginalOrderId but different values in other columns. For example

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge|101|...
3|13_Qrta|refund|94|...
4|15_Utq1|charge|56|...

I would like for each unique pair of MercahntContract OriginalOrderId aggregate data in one row, so above resuklt would look like

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge,refund|101,94|...
4|15_Utq1|charge|56|...

I was looking around but unfortunately couldn't find solution. I was trying to modify first FROM to

SELECT ...
(SELECT * FROM Deal d1 WHERE d1.Id=(SELECT MAX(Id) FROM Deal d2 "+
    "WHERE d2.OriginalOrderId=d1.OriginalOrderId)) d 
LEFT JOIN ...

But it didn't give the required result. Would really appreciate if anybody could give any hints as to how go about my problem.

0

1 Answer 1

0

Use For XML Path..

select 
Merchant_contract,
OriginalOrderid,
Stuff(
(Select ','+ status from yourtable t2 where t2.Merchant_contract=t1.Merchant_contract
and 
t2.orderid=t1.orderid
For Xml Path(''))
,1,2,'') as status
---do similarly for other columns
from 
yourtable t1
group by 
Merchant_contract,
OriginalOrderid

Not the answer you're looking for? Browse other questions tagged or ask your own question.