-2

I currently have data in a table related to transactions. Each record has a purchase ID, a Transaction Number, and up to 5 purchases assigned to the transaction. Associated with each purchase there can be up to 10 transactions. For the first transaction of each purchase I need a field that is a string of each unique purchase concatenated. My solution was slow I estimated it would take 40 days to complete. What would be the most effective way to do this?

Anthony Carmona
  • 1
  • 1
    *"My solution was slow I estimated it would take 40 days to complete"* 40 days for a single SQL statement?! That's ridicously slow. What was the SQL you tried to write? Also, you've given us no sample data, expected results, or anything. We have nothing which we can actually use to help you. – Thom A Jan 02 '20 at 17:00
  • 1
    Please provide the table definition and a desired output. – cdrrr Jan 02 '20 at 17:00
  • And look at STRING_AGG: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15 – David Browne - Microsoft Jan 02 '20 at 17:00

1 Answers1

0

What you are looking for can be achieved in 2 steps:

Step1: Extracting the first transaction of each purchase

Depending upon your table configuration this can be done in a couple of different ways.

If your transaction IDs are sequential, you can use something like:

select * from table a
inner join 
(select purchaseid,min(transactionid) as transactionid 
from table group by purchaseid) b 
on a.purchaseid-b.purchaseid and a.transactionid=b.transactionid

If there is a date variable driving the transaction sequence, then:

select a.* from
(select *,row_number() over(partition by purchaseid order by date) as rownum from table)a 
where a.rownum=1

Step2: Concatenating the Purchase details

This can be done by using the String_agg function if you are using the latest version of SQL server. If not, the following link highlights a couple of different ways you can do this:

Optimal way to concatenate/aggregate strings

Hope this helps.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13