0

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".

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_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.

Mistu4u
  • 5,132
  • 15
  • 53
  • 91
  • are those the only rows in your table? – Vamsi Prabhala Feb 24 '17 at 01:24
  • No there are many more, this is just an example. – Mistu4u Feb 24 '17 at 01:25
  • 1
    then show more data..what you're asking for is trivial if you don't show appropriate data. – Vamsi Prabhala Feb 24 '17 at 01:26
  • you could try this link: the solutions other than the pivot solution of the selected answer: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – vims liu Feb 24 '17 at 01:28
  • Not clear... what goes in param_value_1 vs. param_value_2? How is that related to the values (A, B) in the first column? If you have many more rows, what do you want the result to be, a single row with many columns? How many columns - 100? 1000? 1 million? more? –  Feb 24 '17 at 01:34
  • @mathguy, Please check the edit. I want to see the values for "A" and "B" only. Something that can end with "param_id in ('A','B')" and returns the result as I described. – Mistu4u Feb 24 '17 at 01:39
  • Why doesn't `PIVOT` serve your purpose? Perhaps it does, but you don't know how to use it? I can suggest a different way, but it's just the old way of pivoting, before the `PIVOT` operator was introduced in Oracle 11.1. Transforming rows into columns is the **definition** of pivoting, but you **are** looking to pivot one way or another. –  Feb 24 '17 at 01:42

1 Answers1

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?)

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):

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;
  • There is a problem using this construct, because the product we use doesn't support using this kind of SQL query. Is there any other way to do so? We are using Oracle 11.2 You were telling pivot, how can we use pivot for this? Because what I understand, pivot can work with only aggregate functions and can give me eg.count for each parameter_id but not the parameter_value. – Mistu4u Feb 24 '17 at 01:56
  • @Mistu4u Which product does not support `MAX` and `CASE` statement? And also for pivot, you could just use `MAX`, not `COUNT`. It will be `PIVOT (MAX(Param_value) FOR Param_id IN ( 'A' AS Param_value_1, 'B' AS Param_value_2)` – Pham X. Bach Feb 24 '17 at 02:07
  • @PhamX.Bach, It's not about MAX or CASE, it's about this specific construct that is used in this answer. I will check using MAX though with PIVOT. – Mistu4u Feb 24 '17 at 02:18
  • `PIVOT` is indeed an aggregate operation (and so is the solution I provided above). But: If `param_id = 'A'` appears exactly once in your table, what do you think is `MAX(param_value)` over the rows where `param_id = 'A'`? Did you **try** a SQL solution with `PIVOT` and it didn't work, or are you just *assuming* it will not work? Try and you will see it does! –  Feb 24 '17 at 02:21
  • What product are you using? And what is the "kind" of SQL that my solution belongs to, that your product does not support? What part of it is not supported? –  Feb 24 '17 at 02:22