不带枢轴算子的局部枢轴 [英] Partial pivot without pivot operator

问题描述

我正在寻找不使用PIVOT运算符(旧的ms-sql服务器)来部分透视表的查询. 表格:

I am looking for a query to partially pivot a table without using the PIVOT operator (old ms-sql server). the table:

id-------item---------rank1---------rank2
231------it1--------- 1 ----------- 1
231------it2-------- 1 ----------- 2 
231------it3--------- 1 ----------- 3
154------it4--------- 3 ----------- 4
154------it2--------- 1 ----------- 2
155------it2--------- 1 ----------- 2
156------it3 -------- 2 ----------- 2
156------it1 -------- 1 ----------- 1

预期结果:

id---------item1----item2----item3---item*... 
231 -------it1------it2---------it3 
154--------it2------it4 
155--------it2 
156--------it1------it3

排序等级1和等级2

我在Google上进行了搜索，但发现的解决方案太复杂了，无法应用.

I searched on google but the solution I found was too complicated to apply.

推荐答案

在SQL Server中，可以使用row_number为每个id组分配行号.然后，您可以使用max(case(...技巧进行旋转:

In SQL Server, you could use row_number to assign a row number for each id group. Then you can use the max(case(... trick to pivot:

select  id
,       max(case when rn = 1 then item end) as item1
,       max(case when rn = 2 then item end) as item2
,       max(case when rn = 3 then item end) as item3
from    (
        select  row_number() over (partition by id
                    order by rank1, rank2) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
group by
        id

如果不使用动态SQL，就无法解决N个项目.

There is no general solution for N items without using dynamic SQL.

这篇关于不带枢轴算子的局部枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！