turn the distinct value of columns into a rows postgres

Question

I have a schema like:

 [ad_id] . [name] . [valueofname]
   1 .       name .   "brian"
   1 .       age  .    "23"
   2 .       job  .    "IT"
   2 .       name .    "Jack"

the row name contains multiple values : age , name, birthday, job, age I'd like to convert it into this:

[ad_id] .   [name]  .      [age] .      [birthday] .    [job]
         [valueofname] [valueofname] [valueofname] [valueofname]

I have done the query for each line:

select * from where name='name'
select * from where name='age'
select * from where name='job'

I saw the example SQL Server : Columns to Rows. But it's the opposite of my problem.

Do you have any suggestion for making one scalable query in term of performance?

Possible duplicate of stackoverflow.com/questions/1241178/mysql-rows-to-columns — Sandesh Gupta
– Sandesh Gupta, Commented Apr 12, 2018 at 10:05
@SandeshGupta thanks for but how can i do just a select without creating a view ?? — Medone
– Medone, Commented Apr 12, 2018 at 10:34
@GordonLinoff i am using both mysql and postgres from my app but the case here is in my postgres — Medone
– Medone, Commented Apr 12, 2018 at 10:57

Gordon Linoff · Accepted Answer · 2018-04-12 10:42:05Z

1

You can use conditional aggregation:

select ad_id,
       max(case when name = 'name' then valueofname end) as name,
       max(case when name = 'age' then valueofname end) as age,
       max(case when name = 'birthday' then valueofname end) as birthday,
       max(case when name = 'job' then valueofname end) as job
from t
group by ad_id;

In SQL Server, you can also do something similar with pivot.

answered Apr 12, 2018 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Medone Over a year ago

Thanks but here the problem i have a string values so the max and sum will not work

Medone Over a year ago

@a_horse_with_no_name what about if i have to do ae WHERE clauson the valueofname to have just type of value needed

Gordon Linoff Over a year ago

@Medone . . . You can cast the value to whatever type you think is appropriate. The data in the database is being stored as a string. However, for retrieving the value (assuming one per ad_id), the above is fine.

Medone Over a year ago

@a_horse_with_no_name if there is no rows returned to replace it as a NULL instead of " " how it would be ? because some names are missing from the database

Collectives™ on Stack Overflow

turn the distinct value of columns into a rows postgres

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related