I have the following table :
Column1 | Column2 | Column3 | Column4 | Account |
---|---|---|---|---|
One | Two | Three | Data1 | Acc1 |
One | Two | Three | Data2 | Acc2 |
One | Two | Three | Data3 | Acc3 |
Five | Six | Seven | Data3 | Acc4 |
Five | Six | Seven | Data1 | Acc1 |
And I would like to make a query to obtain the following result :
Column 1 | Column2 | Column3 | Data1 | Data2 | Data3 |
---|---|---|---|---|---|
One | Two | Three | Acc1 | Acc2 | Acc3 |
Five | Six | Seven | Acc1 | NULL | Acc4 |
I tried something like :
select Column1, Column2, Column3,
case
when Column4 = 'Data1' then Account
end as Data1,
case
when Column4 = 'Data2' then Account
end as Data2,
case
when Column4 = 'Data3' then Account
end as Data3
from mytable group by Column1, Column2, Column3
But instead I got the following :
Column 1 | Column2 | Column3 | Data1 | Data2 | Data3 |
---|---|---|---|---|---|
One | Two | Three | Acc1 | NULL | NULL |
Five | Six | Seven | NULL | NULL | Acc4 |
It seems that group by
stops at the first distinct line, therefore it stops reading lines with One, Two Three
once it found one, and of course same with Five, Six, Seven
, therefore it only fills the Data
columns according to the first match.
How can I get to my desired behaviour ?
Thanks in advance.