CODEWITHSUNDEEP
sqlpivot
user2123852
user2123852

Reputation: 59

SQL row to column transposing

I am facing a problem in transposing column to rows,Pivoting seems not to be giving me the result in a way I want to show.

Here is the sample data pattern having n number of rows:

Coulmn1      Column2
Customer     C1
Account      A1
Transaction  T1
Transaction  T2
Transaction  T3
Account      A2
Transaction  T11
Transaction  T12
Transaction  T13
Customer     C2
Account      A11
Transaction  T111
Transaction  T112
Transaction  T113
Account      A12
Transaction  T1111
Transaction  T1112
Transaction  T1113

I would like to transpose it to below format

Customer  Account  Transaction
C1        A1       T1
C1        A1       T2
C1        A1       T3
C1        A2       T11
C1        A2       T12
C1        A2       T13
C2        A11      T111
C2        A11      T112
C2        A11      T113
C2        A12      T1111
C2        A12      T1112
C2        A12      T1113

Upvotes: 1

Views: 597

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Here is the MySQL version of what you want to do. It uses correlated subqueries to get the customer and account information:

select t.Column1,
       (select column2 from t t2 where t2.column1 = 'Customer' and t2.id <= t.id order by t2.id desc limit 1
       ) as customer,
       (select column2 from t t2 where t2.column1 = 'Account' and t2.id <= t.id order by t2.id desc limit 1
       ) as transaction,
from t
where t.column1= 'Transaction'

This assumes that you have an id column for ordering the rows. If not, then you need to figure out a way to get such a column (either an id or a timestamp), because SQL tables are inherently unordered.

The logic would be similar in other databases. However, instead of limit 1, the syntax might be select top 1 or where rownum = 1 or fetch only 1 row.

Upvotes: 1

Related Questions