User-595703101 posted
Merhaba Aliyilmaz,
Please check following SQL code sample
/*
create table Product (
ProductId int,
Code varchar(10)
)
create table Product_SpecificationAttribute_Mapping (
ProductId int,
AttributeId int
)
truncate table product
insert into Product values (1,'AAAAA'),(2,'BBBBB')
insert into Product_SpecificationAttribute_Mapping values (1,11),(1,12),(1,13),(2,29),(2,28)
*/
with cte as (
select
p.ProductId,
a.AttributeId,
columnid = ROW_NUMBER() over (partition by a.ProductId order by a.AttributeId)
from Product p
left join Product_SpecificationAttribute_Mapping a
on p.ProductId = a.ProductId
)
select
ProductId,
max(Attribute_1) as Attribute_1,
max(Attribute_2) as Attribute_2,
max(Attribute_3) as Attribute_3,
max(Attribute_4) as Attribute_4,
max(Attribute_5) as Attribute_5
from (
select
ProductId,
case when columnid = 1 then AttributeId end as Attribute_1,
case when columnid = 2 then AttributeId end as Attribute_2,
case when columnid = 3 then AttributeId end as Attribute_3,
case when columnid = 4 then AttributeId end as Attribute_4,
case when columnid = 5 then AttributeId end as Attribute_5
from cte
) tbl
group by ProductId
This will display attributes of a product in 5 side by side columns named Attribute_1 ... Attribute_5
Please refer to
Display Data in Multiple Columns using SQL tutorial for a similar use case where data of a single column is displayed in multiple columns
You will see, it is similar to a
SQL PIVOT query but for pivot we are actually using values of the attributes. This is the difference