在单行中的不同列中获取不同的行值

时间:2022-02-24 13:00:40

My table looks like this:

我的表看起来像这样:

Param_id     Param_value
------------------------
A             1
B             2
C             3
D             4

.... and so on. Now I want only the values of Param_id "A" and "B".

.... 等等。现在我只想要Param_id“A”和“B”的值。

Now I want to get the param_value in two different columns instead of two different rows. But if I use IN clause it will return the result in two rows. I want something like the below:

现在我想将param_value放在两个不同的列而不是两个不同的行中。但是,如果我使用IN子句,它将返回两行结果。我想要类似下面的东西:

Param_value_1       Param_value_2
---------------------------------
1                   2

I can't use listagg or pivot because they are not serving my purpose. Is there any other way to achieve this? I searched in Google but could not find any solution for this.

我不能使用listagg或pivot,因为它们不符合我的目的。有没有其他方法来实现这一目标?我在谷歌搜索但找不到任何解决方案。

1 个解决方案

#1


1  

The old way of pivoting... Since you are looking for the parameter values for parameter_id in ('A', 'B'), it doesn't make much sense to name the resulting columns param_value_1 and param_value_2; why not param_value_a and param_value_b? (Otherwise what determines that 'A' is 1 and 'B' is 2, and not the other way around?)

旧的转动方式...由于您在('A','B')中查找parameter_id的参数值,因此将结果列命名为param_value_1和param_value_2没有多大意义;为什么不param_value_a和param_value_b? (否则是什么决定'A'是1而'B'是2,而不是相反?)

So - back to the old way of pivoting (although I suspect PIVOT will work too, regardless of requirement - unless you are on Oracle 10 or lower):

所以 - 回到旧的旋转方式(虽然我怀疑PIVOT也可以工作,无论要求如何 - 除非您使用的是Oracle 10或更低版本):

select max(case when param_id = 'A' then param_value end) as param_value_a,
       max(case when param_id = 'B' then param_value end) as param_value_b
from   your_table;

#1


1  

The old way of pivoting... Since you are looking for the parameter values for parameter_id in ('A', 'B'), it doesn't make much sense to name the resulting columns param_value_1 and param_value_2; why not param_value_a and param_value_b? (Otherwise what determines that 'A' is 1 and 'B' is 2, and not the other way around?)

旧的转动方式...由于您在('A','B')中查找parameter_id的参数值,因此将结果列命名为param_value_1和param_value_2没有多大意义;为什么不param_value_a和param_value_b? (否则是什么决定'A'是1而'B'是2,而不是相反?)

So - back to the old way of pivoting (although I suspect PIVOT will work too, regardless of requirement - unless you are on Oracle 10 or lower):

所以 - 回到旧的旋转方式(虽然我怀疑PIVOT也可以工作,无论要求如何 - 除非您使用的是Oracle 10或更低版本):

select max(case when param_id = 'A' then param_value end) as param_value_a,
       max(case when param_id = 'B' then param_value end) as param_value_b
from   your_table;