sql - 如何将值列设置为值行？

Question

我有一个如下表 dbo.tbbuy ：

我想得到如下结果表：

标签： sqlsql-serverpivot-table

Answer 1

SQL 表表示无序集。您的表没有排序，因此您可以使用min()and max()：

select name, min(value) as value1, max(value) as value2
from t
group by name;

更通用的方法使用条件聚合：

select name,
       max(case when seqnum = 1 then value end) as value_1,
       max(case when seqnum = 2 then value end) as value_2,
       max(case when seqnum = 3 then value end) as value_3
from (select t.*,
             row_number() over (partition by name order by name) as seqnum
      from t
     ) t
group by name;

如果您希望值按特定顺序排列，请更改order by子句。