Convert sql row into columns

Question

Hi,

I have a table:

A    B     c
a1   1     a11
a1   2     a12
a1   3     a13
a2   1     a21
a2   2     a22
a2   3     a23

and I want to convert it to:

A     C1     C2     C3
a1    a11    a12    a13
a2    a21    a22    a23

How can I write a SQL query to achieve this... I do not want to convert my table to csv and use python and do it...

Answer 1

Take a look at 'How to pivot rows into columns'.

Answer 2

SELECT A,
MAX(CASE WHEN B=1 THEN c END) AS C1,
MAX(CASE WHEN B=2 THEN c END) AS C2, // etc for B=3,4
FROM table1
GROUP BY A