List uCommerce product data in an importable format

List Product Data.sql

USE MarkerTechV2CMS
GO

WITH Fields
AS
    (
        SELECT
            pd.ProductDefinitionId [ChildDefinitionID]
          , pd.ProductDefinitionId
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
          , pdf.Deleted
          , pdf.IsVariantProperty
          , pdf.Multilingual
		  , pdf.SortOrder
        FROM
            uCommerce_ProductDefinition pd
                LEFT JOIN uCommerce_ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
        WHERE
            pd.Deleted = 0

        UNION ALL

        SELECT
            f.ProductDefinitionId
          , pd.ProductDefinitionId [ChildDefinitionID]
          , f.ProductDefinitionName AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
          , pdf.Deleted
          , pdf.IsVariantProperty
          , pdf.Multilingual
		  , pdf.SortOrder
        FROM
            Fields f
                JOIN uCommerce_ProductDefinitionRelation pdr ON f.ChildDefinitionID = pdr.ProductDefinitionId
                JOIN uCommerce_ProductDefinition pd ON pdr.ParentProductDefinitionId = pd.ProductDefinitionId
                JOIN uCommerce_ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
        WHERE
            pd.Deleted = 0

    )
SELECT DISTINCT
    ProductDefinitionName
  , ProductDefinitionId
  , ProductDefinitionFieldName
  , ProductDefinitionName + ProductDefinitionFieldName AS [Key]
  , IsVariantProperty
  , Multilingual
  , ProductDefinitionFieldId
  , SortOrder
INTO
	#ProductDefinitionFields
FROM
    Fields
WHERE
    ProductDefinitionFieldId IS NOT NULL
    AND Deleted = 0
ORDER BY
    ProductDefinitionName;

WITH Tree AS (
	SELECT
		CategoryId, Name, ParentCategoryId, 0 AS [Level], CAST(QUOTENAME(p.[Name]) AS NVARCHAR(MAX)) AS [Resource Name], SortOrder
	FROM
		dbo.uCommerce_Category p
	WHERE
		ParentCategoryId IS NULL

	UNION ALL

	SELECT
		c.CategoryId, c.Name, c.ParentCategoryId, ct.[Level] + 1 AS [Level], 
		CAST(ct.[Resource Name] + ' > ' + QUOTENAME(c.Name) AS NVARCHAR(MAX)) AS [Resource Name]
		, c.SortOrder
	FROM
		dbo.uCommerce_Category AS c INNER JOIN Tree AS ct ON c.ParentCategoryId = ct.CategoryId
)
SELECT 
	pvt.ProductId, 
	ISNULL([1],'') AS [Brand Category], 
	ISNULL([2],'') AS [Category 1], 
	ISNULL([3],'') AS [Category 2], 
	ISNULL([4],'') AS [Category 3], 
	ISNULL([5],'') AS [Category 4],
	ISNULL([6],'') AS [Category 5], 
	ISNULL([7],'') AS [Category 6], 
	ISNULL([8],'') AS [Category 7], 
	ISNULL([9],'') AS [Category 8], 
	ISNULL([10],'') AS [Category 9], 
	ISNULL([11],'') AS [Category 10]
INTO
	#Categories
FROM 
(
	(
		SELECT
			p.ProductId
			, p.[Name]
			, ct.[Resource Name]
			, 1 AS RowNumber
		FROM 
			uCommerce_Product p
				LEFT JOIN uCommerce_CategoryProductRelation xpc ON p.ProductId = xpc.ProductId
				LEFT JOIN Tree ct ON xpc.CategoryId = ct.CategoryId
		WHERE
			ct.[Resource Name] LIKE '\[Brands\]%' ESCAPE '\'
	)
UNION ALL
	(
		SELECT
			p.ProductId
			, p.[Name]
			, ct.[Resource Name]
			, ROW_NUMBER() OVER(
				PARTITION BY xpc.ProductId 
				ORDER BY ct.[Resource Name] ASC
				)  + 1
				AS RowNumber
		FROM 
			uCommerce_Product p
				LEFT JOIN uCommerce_CategoryProductRelation xpc ON p.ProductId = xpc.ProductId
				LEFT JOIN Tree ct ON xpc.CategoryId = ct.CategoryId
		WHERE
			ct.[Resource Name] NOT LIKE '\[Brands\]%' ESCAPE '\'
	)
UNION ALL
	(
		SELECT
			p.ProductId
			, p.[Name]
			, ''
			, 2 AS RowNumber
		FROM 
			uCommerce_Product p
				LEFT JOIN uCommerce_CategoryProductRelation xpc ON p.ProductId = xpc.ProductId
		WHERE
			xpc.CategoryId IS NULL
	)
) AS Src
PIVOT
(
	MAX([Resource Name])
	FOR src.RowNumber
	IN  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS Pvt;

SELECT d.ProductId, d.ProductDefinitionFieldId, d.[Value]
INTO #Properties
FROM 
(
	SELECT
		pp.ProductId
	,	pdf.ProductDefinitionFieldId
	,	pp.[Value]
	FROM
		uCommerce_ProductProperty pp
			LEFT JOIN uCommerce_ProductDefinitionField pdf ON pp.ProductDefinitionFieldId = pdf.ProductDefinitionFieldId
	WHERE
		pdf.Multilingual = 0
		AND pp.ProductDefinitionFieldId IS NOT NULL
	UNION ALL

	SELECT
		pd.ProductId
	,	pdf.ProductDefinitionFieldId
	,	pdp.[Value]
	FROM
		uCommerce_ProductDescriptionProperty pdp
			LEFT JOIN uCommerce_ProductDefinitionField pdf ON pdp.ProductDefinitionFieldId = pdf.ProductDefinitionFieldId
			LEFT JOIN uCommerce_ProductDescription pd ON pdp.ProductDescriptionId = pd.ProductDescriptionId
	WHERE
		pdf.Multilingual = 1
		AND pdp.ProductDefinitionFieldId IS NOT NULL
) AS d
	
;

DECLARE @cols AS NVARCHAR(MAX)
		, @niceCols AS NVARCHAR(MAX)
		, @sql AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(f.ProductDefinitionFieldId) 
            FROM #ProductDefinitionFields f
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @niceCols = STUFF((SELECT ',' + QUOTENAME(f.ProductDefinitionFieldId) + ' AS ' + QUOTENAME(f.ProductDefinitionFieldName)
            FROM #ProductDefinitionFields f
			GROUP BY ',' + QUOTENAME(f.ProductDefinitionFieldId) + ' AS ' + QUOTENAME(f.ProductDefinitionFieldName), f.SortOrder
			ORDER BY f.SortOrder ASC, ',' + QUOTENAME(f.ProductDefinitionFieldId) + ' AS ' + QUOTENAME(f.ProductDefinitionFieldName) ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @sql = 'SELECT 
	Sku
	,   VariantSku
	,   ProductType
	,	LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(DisplayName,''''), CHAR(9), '' ''), CHAR(10), '' ''), CHAR(13), '' ''))) AS DisplayName
	,	LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(ShortDescription,''''), CHAR(9), '' ''), CHAR(10), '' ''), CHAR(13), '' ''))) AS ShortDescription
	,	LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(LongDescription,''''), CHAR(9), '' ''), CHAR(10), '' ''), CHAR(13), '' ''))) AS LongDescription
	,	CASE WHEN DisplayOnSite=1 THEN ''Y'' ELSE ''N'' END AS [Display On Site]
	,	CASE WHEN AllowOrdering=1 THEN ''Y'' ELSE ''N'' END AS [Allow Ordering]
	,	Price
	,	ThumbnailImageMediaId AS [Product Listing Image Id]
	,	PrimaryImageMediaId AS [Gallery Image Folder Id]
	,	[Brand Category]
	,	[Category 1]
	,	[Category 2]
	,	[Category 3]
	,	[Category 4]
	--,	[Category 5]
	--,	[Category 6]
	--,	[Category 7]
	--,	[Category 8]
	--,	[Category 9]
	--,	[Category 10]
	,' + @niceCols + ' 
FROM (
	SELECT
	p.ProductId
	,	p.Sku
	,	p.VariantSku
	,	p.[Name]
	,	p.DisplayOnSite
	,	p.AllowOrdering
	,	pgp.Price
	,	pd.DisplayName
	,	pd.ShortDescription
	,	pd.LongDescription
	,   pp.ProductDefinitionFieldId
	,	LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(pp.[Value],''''), CHAR(9), '' ''), CHAR(10), '' ''), CHAR(13), '' ''))) AS Value
	,   pdf.[Name] AS [ProductType]
	,	p.ThumbnailImageMediaId
	,	p.PrimaryImageMediaId
	,	[Brand Category]
	,	[Category 1]
	,	[Category 2]
	,	[Category 3]
	,	[Category 4]
	,	[Category 5]
	,	[Category 6]
	,	[Category 7]
	,	[Category 8]
	,	[Category 9]
	,	[Category 10]
	FROM
		uCommerce_Product p
			LEFT JOIN #Properties pp ON p.ProductId = pp.ProductId
			LEFT JOIN uCommerce_ProductDescription pd ON p.ProductId = pd.ProductId
			LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId
			LEFT JOIN uCommerce_ProductDefinition pdf ON p.ProductDefinitionId = pdf.ProductDefinitionId
			LEFT JOIN #Categories c ON p.ProductId = c.ProductId
	--WHERE
	--	p.Sku IN (''PA02-Disc-BW'', ''1024140000'',''9027310000'',''1274660000'')
) x
PIVOT
(
	MAX(Value)
	FOR ProductDefinitionFieldId in (' + @cols + ')
) p'

PRINT @sql
EXECUTE (@sql)

DROP TABLE #ProductDefinitionFields
DROP TABLE #Categories
DROP TABLE #Properties