根据索引范围将一列转换为多列

Question

Server中有下表：

| idx | value |
| --- | ----- |
| 1   | N     |
| 2   | C     |
| 3   | C     |
| 4   | P     |
| 5   | N     |
| 6   | N     |
| 7   | C     |
| 8   | N     |
| 9   | P     |

我想说的是：

| idx 1-3 | idx 4-6 | idx 7-9 |
| ------- | ------- | ------- |
| N       | P       | C       |
| C       | N       | N       |
| C       | N       | P       |

我该怎么做？

Answer 1

如果您想将数据分成三列，按id顺序排列--并假设id从1开始，没有空白--那么您可以在特定的数据上使用：

select max(case when (idx - 1) / 3 = 0 then value end) as grp_1,
       max(case when (idx - 1) / 3 = 1 then value end) as grp_2,
       max(case when (idx - 1) / 3 = 2 then value end) as grp_3
from t
group by idx % 3
order by min(idx);

上面没有硬编码范围，但是"3“在不同的上下文中意味着不同的东西--有时是列的数量，有时是结果集中的行数。

但是，以下内容进行了概括，以便在需要时添加额外的行：

select max(case when (idx - 1) / num_rows = 0 then idx end) as grp_1,
       max(case when (idx - 1) / num_rows = 1 then idx end) as grp_2,
       max(case when (idx - 1) / num_rows = 2 then idx end) as grp_3
from (select t.*, convert(int, ceiling(count(*) over () / 3.0)) as num_rows
      from t
     ) t
group by idx % num_rows
order by min(idx);

这里是db<>fiddle。

Answer 2

您可以使用横向联接计算每一行的类别，然后枚举每个类别中的行，最后使用条件聚合进行支点：

select 
    max(case when cat = 'idx_1_3' then value end) as idx_1_3,
    max(case when cat = 'idx_4_6' then value end) as idx_4_6,
    max(case when cat = 'idx_7_9' then value end) as idx_7_9
from (
    select t.*, row_number() over(partition by v.cat) as rn
    from mytable t
    cross apply (values (
        case 
            when idx between 1 and 3 then 'idx_1_3'
            when idx between 4 and 6 then 'idx_4_6'
            when idx between 7 and 9 then 'idx_7_9'
        end
    )) v(cat)
) t
group by rn

Answer 3

您可以按以下方式使用该模块：

select max(case when idx between 1 and 3 then value end) as idx_1_3,
       max(case when idx between 4 and 6 then value end) as idx_4_6,
       max(case when idx between 7 and 9 then value end) as idx_7_9
  from t
group by (idx-1) % 3;

如果您的idx不是连续数，那么使用以下方法代替from t

from (select value, row_number() over(order by idx) as idx
   from your_table t) t