sql17 -为't‘多次指定了列'id’

Question

我在mssm 17中写了下面的查询。

select t.id,
max (case when seq=1 then label end) as label-1,
max (case when seq=2 then label end) as label-2,
max (case when seq=3 then label end) as label-3,
max (case when seq=4 then label end) as label-4,
max (case when seq=5 then label end) as label-5,
from (select t1.*,t2.*,
row-number () over (partition by t1.id order by t2.label) as seq
from table1 t1 inner join table2 t2 on t1.id=t2.sequence
) t
group by t.id

查询没有执行，错误是:为't‘多次指定了列'id’。我想将具有相似id的所有行合并到一行不同的列中。有人能帮我解决这个错误吗？

Answer 1

您的查询有多个问题。这个特定的问题是id在table1和table2中都存在。因此，只需在外部查询中选择所需的列。

此外，列别名不允许使用-。正确的函数名是row_number()，而不是row-number()

select t.id,
      max(case when seq=1 then label end) as label_1,
      max(case when seq=2 then label end) as label_2,
      max(case when seq=3 then label end) as label_3,
      max(case when seq=4 then label end) as label_4,
      max(case when seq=5 then label end) as label_5,
from (select t1.id, t2.label,
             row_number() over (partition by t1.id order by t2.label) as seq
      from table1 t1 inner join 
           table2 t2
           on t1.id = t2.sequence
      ) t
group by t.id