Object Attributed Mapping Interview Question, Sql Server

Object Attributed Mapping Interview Question

/*
Question 1 : You are given following set of tables
Object{obj_id, obj_name,....<Other object related details>}
Attribute{att_id, att_name,....<Other attribute related details>}
ObjectAttributeMapping{objAtt_id, obj_id, att_id, att_value}

You have to provide the output in following format
Output table with column name {obj_id, obj_name, att_name1, att_name2, att_name3,...}
each object should only be represented in one row, and att_name1 column will have att_id1 values,
from ObjectAttributeMapping table, 
Similarly att_name2 column will have value of att_id2 from ObjectAttributeMapping 
*/
CREATE TABLE object( id int PRIMARY KEY,name varchar(50) NOT NULL,);
insert into object values(1,'Cube')
insert into object values(2,'Sqr')
insert into object values(3,'Matrix')

Create table attributes(id int Primary key,name varchar(50) not null)
insert into attributes values(1,'color')
insert into attributes values(2,'height')
insert into attributes values(3,'length')
insert into attributes values(4,'width')

Create table object_attributes_mapping
(
    id int Primary key,
    obid int Foreign key REFERENCES object(id) ,
    attid int Foreign key REFERENCES attributes(id) ,
    attvalue varchar(50) not null
)

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

select * from object;
select * from attributes;
select * from object_attributes_mapping;

select * from 
(
    select b.id,b.name,c.name as attribute_name,a.attvalue 
    from object_attributes_mapping as a join Object as b 
    on a.obid = b.id join Attributes as c 
    on a.attid =c.id
   
) as t
pivot
(
    max(attvalue)
    for attribute_name in(color,height,length,width)
)a

order by id


run \| edit \| history \| help	0