时间戳

首页 > 解决方案 > 单表的 SQL 透视图

sql - 单表的 SQL 透视图

问题描述

有一张桌子：

Nomber    Sce    SceValue
10        A      a1b2c3
20        C      d2v3b4
10        B      42b2c3
10        B      5978c3
20        A      edr432

我需要使用视图创建以下列表，其中为每个单独的“Nomber”显示所有可能的“Sce”和“SceValue”对（最多 9 对）：

Nomber    Sce1    SceValue1    Sce2    SceValue2  ...    Sce9    SceValue9
10        A       a1b2c3       B       42b2c3            B       5978c3
20        C       d2v3b4       A       edr432

我想使用视图来实现这一点。这可能吗？

标签： sqlsql-serverview

解决方案

您可以使用条件聚合：

select number,
       max(case when seqnum = 1 then Sce end) as sce_1,
       max(case when seqnum = 1 then SceValue end) as SceValue_1,
       max(case when seqnum = 2 then Sce end) as sce_2,
       max(case when seqnum = 2 then SceValue end) as SceValue_2,
       . . .
       max(case when seqnum = 9 then Sce end) as sce_9,
       max(case when seqnum = 9 then SceValue end) as SceValue_9
from (select t.*,
             row_number() over (partition by nomber order by sce) as seqnum
      from t
     ) t
group by nomber;

推荐阅读