I am using PostgreSQL Database and here is the Sample Data. It is a large dataset and I am using the first two rows for example.

Price | Moduletype
172   | 1
173   | 2  

What I want is to query the data based on the Moduletype numbers 1 and 2. Moduletype1 will have the prices belonging to it and Moduletype2 will have prices belonging to it.

For Example:

Moduletype1 | ModuleType2
172         | 173
  • Can you edit the question to post exactly what you'd want as a result? It sounds like you want a single row in the result with two columns, one with a value of 172 and one with a value of 173. Is that correct? Assuming that your actual table has more than two rows, how do you know which rows you want to combine? – Justin Cave Apr 22 at 19:36
  • 1
    Let us continue this discussion in chat. – Uttasarga Singh Apr 22 at 19:50

The following assumes you never have more than one price per moduletype:

select max(price) filter (where moduletype = 1) as module_type_1,
       max(price) filter (where moduletype = 2) as module_type_2
from the_table;

If you want to see multiple rows per moduletype, maybe you want this:

select t1.price as module_type_1,
       t2.price as module_type_2
from (
  select price, row_number() over (order by price) as rn
  from the_table 
  where moduletype = 1
) t1
  full join (
    select price, row_number() over (order by price) as rn
    from the_table 
    where moduletype = 2
  ) t2 on t1.rn = t2.rn

Or alternatively (same idea, different approach):

select x.*
from (
  select array_agg(price) filter (where moduletype = 1) as moduletype1,
         array_agg(price) filter (where moduletype = 2) as moduletype2
  from the_table
) t
  cross join unnest (t.moduletype1, t.moduletype2) as x (module_type_1, module_type_2)
  • Hey, Thank you for your comment. So I have a large table out of which I posted the first two rows as an example. What I want is to create a view for the two columns named as module1 and module2, which will have all the prices which belongs to module 1 and module2 respectively. – Uttasarga Singh Apr 23 at 16:17

This is the Query which helps to select the Price based on the Module Type.

(case when i.moduletype = 1 then i.PRICE else -1 end)RTL_PRC_AMT,
(case when i.moduletype = 2 then i.PRICE else -1 end)ADMN_FEE_AMT
from TABLE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.