CODEWITHSUNDEEP
sqlsql-serversql-server-2014
RKodakandla
RKodakandla

Reputation: 3484

Transforming row data into columns in SQL Server

I have table with the data in following format.

User ID| Date Paid  | Amount Paid
- - - - - - - - - - - - - - - - - 
1      | 2019-04-01 | 120
1      | 2019-03-01 | 120
1      | 2019-05-01 | 130
2      | 2019-03-01 | 100
2      | 2019-04-01 | 110
3      | 2019-05-01 | 100

I need export this table into a format where all of the records for a single user should be in a single row, and date paid and amount records should start from left with the oldest record first.

User ID | 1st Date Paid | 1st Amount Paid | 2nd Date Paid | 2nd Amount Paid | 3rd Date Paid | 3rd Amount Paid | . . . . . 
- - - - - - - -  - - - - - -  - - - -- - - - - - --  -- - -  -- - - - - - - - 
   1    | 2019-03-01    | 120             | 2019-04-01    | 120             | 2019-05-01 | 130
   2    | 2019-03-01    | 100             | 2019-40-01    | 110             |
   3    | 2019-05-01    | 100             |

There could be up to a maximum of 10 records per user, so the export should contain the date paid and amount paid columns 10 times. If a user doesn't contain 10 records, then those columns will be left empty.

What is the best way to achieve this result?

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use conditional aggregation:

select user_id,
       max(case when seqnum = 1 then date_paid end) as date_paid1,
       max(case when seqnum = 1 then amount_paid end) as amount_paid1,
       max(case when seqnum = 2 then date_paid end) as date_paid2,
       max(case when seqnum = 2 then amount_paid end) as amount_paid2,
       max(case when seqnum = 3 then date_paid end) as date_paid3,
       max(case when seqnum = 3 then amount_paid end) as amount_paid3
from (select t.*,
             row_number() over (partition by user_id order by date_paid) as seqnum
      from t
     ) t
group by user_id;

Upvotes: 1

Related Questions