I apologize if I am not clear enough. I don't speak english and I don't know how to explain it.

I have a table that looks like the one below:

And I want to make it look like this other one:

The determinant here is the that the smallest value for each ID would get assigned to P1, then the second smallest would get assigned to P2 and so on. I currently have the following code, but it fails at identifying the smallest value of each ID to place it in the correct column.

SELECT ID, 
max(case when SEQ=1 then TOTAL end) as 'P1',
max(case when SEQ=2 then TOTAL end) as 'P2',
max(case when SEQ=3 then TOTAL end) as 'P3',
max(case when SEQ=4 then TOTAL end) as 'P4',
max(case when SEQ=5 then TOTAL end) as 'P5',
max(case when SEQ=6 then TOTAL end) as 'P6',
max(case when SEQ=7 then TOTAL end) as 'P7',
max(case when SEQ=8 then TOTAL end) as 'P8',
max(case when SEQ=9 then TOTAL end) as 'P9'
FROM TABLE
group by id

CodePudding user response：

You could use row_number to artificially generate the correct sequence for your query to use:

with s as (
  select *, Row_Number() over(partition by id order by total) as s
  from t
)
select ID, 
 Max(case when s = 1 then TOTAL end) as P1,
 Max(case when s = 2 then TOTAL end) as P2,
 Max(case when s = 3 then TOTAL end) as P3,
 Max(case when s = 4 then TOTAL end) as P4,
 Max(case when s = 5 then TOTAL end) as P5,
 Max(case when s = 6 then TOTAL end) as P6,
 Max(case when s = 7 then TOTAL end) as P7,
 Max(case when s = 8 then TOTAL end) as P8,
 Max(case when s = 9 then TOTAL end) as P9
from s
group by ID;

Demo Fiddle