CareerCup

CREATE TABLE ob
( ID integer PRIMARY KEY,
name varchar(50) NOT NULL,

);
insert into ob values(1,'Cube')
insert into ob values(2,'Sqr')
insert into ob values(3,'Matrix')

Create table att
( ID integer Primary key,
name varchar(50) not null
)
insert into att values(1,'color')
insert into att values(2,'hight')
insert into att values(3,'lenght')
insert into att values(4,'width')

Create table objatt
(ID integer Primary key,
obid integer Foreign key REFERENCES ob(id) ,
attid integer Foreign key REFERENCES att(id) ,
attvalue varchar(50) not null
)

insert into objatt values(1,1,1,'red')
insert into objatt values(2,1,2,10)
insert into objatt values(3,1,3,12)
insert into objatt values(4,1,4,5)
insert into objatt values(5,2,1,'green')
insert into objatt values(6,2,2,6)
insert into objatt values(7,3,3,5)
insert into objatt values(8,3,4,9)


Query:
select id,name,
max(case when attid = 1 then attvalue end) color,
max(case when attid = 2 then attvalue end)hight,
max(case when attid = 3 then attvalue end)lenght,
max(case when attid = 4 then attvalue end)width
from(select ob.id, ob.name, objatt.attvalue, objatt.attid
from ob left outer join objatt on ob.id = objatt.obid
)d
group by id,name

select d,listagg(nvl(attvalue,'null'),',') within group(order by name) from (
select ob.name as d,att.name, attvalue from ob join att on 1=1 left join objatt on ob.id=objatt.obid and att.id=attid)tt group by tt.d

select d,listagg(nvl(attvalue,'null'),',') within group(order by name) from (
select ob.name as d,att.name, attvalue from ob join att on 1=1 left join objatt on ob.id=objatt.obid and att.id=attid)tt group by tt.d

;with cte as (
select oam.*, o.obj_name, a.att_name from objattmapping oam
inner join obj o
on oam.obj_id = o.obj_id
inner join att a
on oam.att_id = a.att_id
)
select obj_id, obj_name, max([color]),max([height]), max([length]) ,max([width]) from cte
pivot (max(att_value) for att_name in ([color],[height], [length] ,[width]) ) p
group by obj_id, obj_name

CREATE TABLE Object (obj_id int key, obj_name VARCHAR(10));
CREATE TABLE Attribute (att_id int key, att_name VARCHAR(10));
CREATE TABLE ObjectAttributeMapping (objAtt_id int key, obj_id int, att_id int, att_value VARCHAR(10));
INSERT INTO Object Values (1, 'cube'), (2, 'square'), (3, 'matrix');
INSERT INTO Attribute VALUES (1, 'color'), (2, 'height'), (3, 'length'), (4, 'width');
INSERT INTO ObjectAttributeMapping Values (1, 1, 1, 'red'), (2, 1, 2, '10'), (3, 1, 3, '12'), (4, 1, 4, '5'), (5, 2, 1, 'green'), (6, 2, 2, '6'), (7, 3, 3, '5'), (8, 3, 4, '9');

SELECT O.obj_id, O.obj_name, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='color' AND Oc.obj_id=O.obj_id) color, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='height' AND Oc.obj_id=O.obj_id) height, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='length' AND Oc.obj_id=O.obj_id) length, (SELECT OAc.att_value FROM Object Oc NATURAL JOIN ObjectAttributeMapping OAc NATURAL JOIN Attribute Ac WHERE Ac.att_name='width' AND Oc.obj_id=O.obj_id) width FROM Object O;

select * from
(
select o.Obj_id,Obj_name,Att_name,Att_value from Object1 o cross join Attribute a
left outer join ObjectAttributeMapping oa
on o.Obj_id=oa.Obj_id and a.Att_id=oa.Att_id
) A
PIVOT 
(
	Min(Att_value)
	FOR 
	Att_name
	IN( [color],[height],[length],[width])
)PivotTable
order by Obj_id

select * From (select b.obj_id,b.obj_name,c.Att_name,a.att_value 
From ObjectAttributrMapping as a join Object as b on a.obj_id = b.obj_id
join Attribute as c on a.att_id =c.Att_id) as t
pivot( max(att_value) for Att_name in (color,height,length,width) ) a
order by obj_id

select * from 
(
select o.obj_id,o.obj_name,a.att_name,m.att_value
from 
object o,
attribute a,
oam m
where
m.obj_id = o.obj_id
AND a.att_id = m.att_id
AND m.att_id = a.att_id
)
PIVOT
(
max(att_value)
FOR att_name IN ('color','height','length','width')
)
order by obj_id,obj_name;

select
distinct
ob.id as object_id
,ob.name as object_name
,A.color
,A.height
,A.length
,A.width
from ob
left outer join
(select
objatt.obid
,string_agg(case when att.name='color' then attvalue else null end,'') as color
,string_agg(case when att.name='hight' then attvalue else null end,'') as height
,string_agg(case when att.name='lenght' then attvalue else null end,'') as length
,string_agg(case when att.name='width' then attvalue else null end,'') as width
from objatt
left outer join att on
objatt.attid=att.id
group by objatt.obid) A on
ob.id=A.obid