CODEWITHSUNDEEP
sqlpostgresql
orange
orange

Reputation: 8090

Joining rows into column

I have 3 tables that look like below that I want to join together so that the attribute table's rows become columns of the result table.

block
id  name
1   a
2   b
3   c
4   d

attr_value
id   value  block_id attr_type_id
1    0.1    1         1
2    0.2    1         2
3    0.3    2         3
4    0.4    3         4

attr_type
id   name
1    type_a
2    type_b
3    type_c
4    type_d


result table
block id  name   type_a  type_b  type_c  type_d 
1         a      0.1     0.2     null    null
2         b      null    null    0.3     null
3         c      null    null    null    0.4
4         d      null    null    null    null

I'm using postgresql and tried case when but this duplicated some columns (i.e. multiple block rows with id=1) which I didn't want.

Upvotes: 0

Views: 72

Answers (2)

Sharma Dhananjay
Sharma Dhananjay

Reputation: 437

CREATE TEMPORARY TABLE result_table
(
    blockid bigint,
    name varchar(10),
    type_a float default 1,
    type_b float default 2,
    type_c float default 3,
    type_d float default 4
)

insert into result_table(blockid,name)select id,name from block

update result_table
set type_a=(select value from attr_values a where a.block_id=blockid and a.attr_type_id=type_a)
,type_b=(select value from attr_values a where a.block_id=blockid and a.attr_type_id=type_b)
,type_c=(select value from attr_values a where a.block_id=blockid and a.attr_type_id=type_c)
,type_d=(select value from attr_values a where a.block_id=blockid and a.attr_type_id=type_d)


select * from result_table

drop table result_table

Upvotes: 1

Sharma Dhananjay
Sharma Dhananjay

Reputation: 437

select  b.id,b.name
,(select a.value from attr_value a inner join attr_type at on at.id=a.attr_type_id where b.id=a.block_id and at.id=1) as 'type_a'
,(select a.value from attr_value a inner join attr_type at on at.id=a.attr_type_id where b.id=a.block_id and at.id=2) as 'type_b'
,(select a.value from attr_value a inner join attr_type at on at.id=a.attr_type_id where b.id=a.block_id and at.id=3) as 'type_c'
,(select a.value from attr_value a inner join attr_type at on at.id=a.attr_type_id where b.id=a.block_id and at.id=4) as 'type_d'
from block b

Upvotes: 3

Related Questions