SQL 行列转换

Table A have three columns, for each ID group, there are one or two or three codes (three is max), and for each code, there is a specific value associated with it, please write a sql to get the data in table B, where Code1 is the Code whose values is the smallest, value1 is the value of code1,the value of codes2 greater that code1, and code3’s values greater than code2s’. If there are no three codes for this ID groups, than patch NULL.(ID group could be many, so the sql need to be able tohandle many id groups, like 10000)

创建表和插入数据不再赘述

select ID,
max(case temp.rownumber when 1 then temp.Code  end) as code1,
max(case temp.rownumber when 1 then temp.Value end) as value1,
min(case temp.rownumber when 2 then temp.Code end) as Code2 ,
min(case temp.rownumber when 2 then temp.Value end) as value2,
min(case temp.rownumber when 3 then temp.Code end) as Code3,
min(case temp.rownumber when 3 then temp.Value end) as value3
from
(
select ID, Code , Value,
ROW_NUMBER() over(partition by id order by value) 
as rownumber from TableA 

) as temp
group by ID