WITH Attribs
AS
(
	SELECT Id, Author, ISBN, Pages,Image_URL
	FROM
	(
		SELECT SAO.Id, SA.Name AS [Type], SAO.Name AS TypeValue
		FROM [uva].[dbo].[SpecificationAttribute] SA
			JOIN [uva].[dbo].[SpecificationAttributeOption] SAO
				ON SA.Id = SAO.SpecificationAttributeId
	) AS S
	PIVOT
	(
		MAX(TypeValue)
		FOR [Type] IN (Author,ISBN,Pages,Image_URL)
	) AS P
)	
SELECT
	PRD.Id AS ProductID
	,PRD.Name AS ProductName
	,PRD.ShortDescription
	,PRD.FullDescription
	,PRD.AdminComment
	,A.Author, A.ISBN, A.Pages, A.Image_URL
	,PRDV.Price
	,PRDV.StockQuantity
	,PRDV.Sku 
FROM [uva].[dbo].[Product] PRD
	JOIN [uva].[dbo].[ProductVariant] PRDV
		ON PRD.Id = PRDV.ProductId
	JOIN [uva].[dbo].[Product_SpecificationAttribute_Mapping] PSM
		ON PRD.Id = PSM.ProductId
	JOIN Attribs A
		ON PSM.SpecificationAttributeOptionId = A.Id;