I would like to transform a dataset containing some bank transactions. The ultimate goal is to make a report in Power BI to track daily expenses.

For this, I have the following situation that gives me a headache. :)

This is an example:

The desired output would be to transpose all rows in Transaction_Details that have NULL values in Date column, into a new column (e.g Other_Details) and for each transaction to add another column with "Transaction_Key". Below, I have attached an example:

I used some COALESCE functions but it didn't work.

CodePudding user response：

If we can assume you are able to create an Id/Sequence either in the data source or when importing the data, such that you end up with an incrementing number per row, then by using a windowed aggregation as follows you can convert your data as required:

select Transaction_Key, 
    Max(Date) Date, 
    Max(case when date is not null then Transaction_Details end) Transaction_Details,
    String_Agg(case when date is null then Transaction_Details end, ',') Other_details,
    Max(case when date is not null then Debit end) Debit,
    Max(case when date is not null then Credit end) Credit
from (
    select *, 
      Sum(case when date is null then 0 else 1 end) over(order by id) as Transaction_Key
    from t
)t
group by Transaction_Key;

See this example Fiddle