List all Ucommerce products with their properties

List Products.sql

DECLARE @cols AS NVARCHAR(MAX), @nullLessCols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = STUFF((
			SELECT ',' + QUOTENAME(pdf.Name)
            FROM uCommerce_ProductDefinitionField AS pdf
			WHERE pdf.Deleted=0
			ORDER BY pdf.SortOrder
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = '
SELECT TOP 10 p.ProductId, p.Sku, p.VariantSku, p.Name, ' + @cols  + '
FROM 
(
	SELECT pp.ProductId, pdf.Name, pp.[Value]
	FROM dbo.uCommerce_ProductProperty pp LEFT JOIN uCommerce_ProductDefinitionField pdf ON pp.ProductDefinitionFieldId = pdf.ProductDefinitionFieldId
) AS x
pivot 
(
    MAX([Value])
    FOR [Name] in (' + @cols + ')
) AS pvt
	JOIN uCommerce_Product p ON p.ProductId=pvt.ProductId
'

print(@query)
execute(@query);