I'm having data like
| columnname | value | table |
|---|---|---|
| a | 1 | X |
| b | 2 | X |
| a | 3 | X |
| b | 4 | X |
| a | 5 | X |
| b | 6 | X |
and need to transform into
| table | a | b |
|---|---|---|
| X | 1 | 2 |
| X | 3 | 4 |
| X | 5 | 6 |
CodePudding user response：
select tbl
,a
,b
from
(
select *, (row_number() over(order by value)-1)/2 as rn
from t
) t
pivot(max(value) for columnname in(a, b)) p
| tbl | a | b |
|---|---|---|
| X | 1 | 2 |
| X | 3 | 4 |
| X | 5 | 6 |
