How to retrieving data as customized format

1. I retrieved some data as below (image 01).

i used this query:

SELECT TOP (3) no, co, cdate, year 
FROM main_backup 
WHERE (no = 41505) 
ORDER BY cdate DESC

2. But I want that, like this type as below (image 02)

Answer

You can use conditional aggregation and window functions:

select no,
       max(case when seqnum = 1 then total end) as total_1,
       max(case when seqnum = 1 then cdate end) as date_1,
       max(case when seqnum = 2 then total end) as total_2,
       max(case when seqnum = 2 then cdate end) as date_2,
       max(case when seqnum = 3 then total end) as total_3,
       max(case when seqnum = 3 then cdate end) as date_3
from (select t.*,
             row_number() over (partition by no order by cdate desc) as seqnum
      from t
     ) t
group by no