How to transpose a table and make aggregation in the same time in SQL server?

Question

I begin with SQL. I writed a code for pivoting my table (from column view to row view). it works fine.

my query takes a 2rows x 195columns and converts it into a 195rows x 3 columns.

select Element = [Key]
    ,New = max(case when time_index=1 then value end)
    ,'Current' = max(case when time_index>=2 then value end)
From  (
    Select [time_index]
            ,B.*
        From  (select * from ifrs17.output_bba where id in (618830,618686)) A
        Cross Apply (
                    Select [Key]
                        ,Value
                    From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                    Where [Key] not in ('time_index')
                    ) B
    ) A
Group By [Key]

in the query you can read "(618830,618686)" it corresponds to the exctraction of 2 lines.

time_index	legal_entity_code	cohort	...	...
1	AAA	50	...	...
2	BBB	55	...	...

question from:https://stackoverflow.com/questions/65844071/how-to-transpose-a-table-and-make-aggregation-in-the-same-time-in-sql-server

Answer 1

Waitting for answers