I would like to query a database table that contains data like this for the distinct articleIDs in each transaction:
TransactionID TransactPos ArticleID
1 1 Article A
1 2 Article B
2 1 Article A
2 2 Article B
3 1 Article A
3 2 Article C
3 3 Article D
3 4 Article E <---
3 5 Article E <---
3 6 Article E <---
3 7 Article F
4 1 Article A <---
4 2 Article A <---
4 3 Article C
What I want as a result are only distinct values for each transaction
So, the desired results would look like this:
TransactionID TransactPos ArticleID
1 1 Article A
1 2 Article B
2 1 Article A
2 2 Article B
3 1 Article A
3 2 Article C
3 3 Article D
3 4/5/6 Article E <--- Distinct value for Transaction 3
3 7 Article F
4 1/2 Article A <--- Distinct value for Transaction 4
4 3 Article C
Is anybody having an idea? I tried around with distinct and group by's, but I had no luck so far.
Given data is in distinct with those 3 columns and if you want to get the output as you mentioned. Try below query to get the same
select TransactionID , string_agg(TransactPos, '/') as TransactPos, ArticleID from yourtable
group by TransactionID,ArticleID