Export all products, properties and categories from uCommerce

A while ago I posted about how to Export products from Ucommerce into a CSV type format. The database schema has changed slightly since so today I'm posting an updated version of the code for v9. I've also added a second script that outputs the categories and list of products that are within the category which may be of use to some.

Export Ucommerce Products

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

SET @cols = STUFF((
            SELECT ',' + QUOTENAME(pdf.Name)
            FROM uCommerce_ProductDefinitionField AS pdf LEFT JOIN uCommerce_ProductDefinition pd ON pdf.ProductDefinitionId = pd.ProductDefinitionId
            WHERE pdf.Deleted='0' AND pd.Deleted='0'
            GROUP BY pdf.Name
            ORDER BY MIN(pdf.SortOrder) ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT 
    pvt.ProductId
    ,    p.Sku
    ,    p.VariantSku
    ,    p.Name
    ,   pgp.Amount AS [Price]
    ,    CASE p.DisplayOnSite WHEN ''1'' THEN ''Y'' ELSE ''N'' END AS [Show On Site]
    ,    CASE p.AllowOrdering WHEN ''1'' THEN ''Y'' ELSE ''N'' END AS [Allow Ordering]
    ,   def.Name AS [Product Type]
    ,    p.CreatedOn
    ,    p.ModifiedOn
    ,    pd.DisplayName
    ,    pd.ShortDescription
    ,    pd.LongDescription
    ,    p.Rating
    , ' + @cols + '
    FROM
    (
        SELECT
                pp.ProductId
            ,    ppdf.Name AS [PropertyName]
            ,    pp.Value AS [PropertyValue]
        FROM
            uCommerce_ProductProperty pp
                LEFT JOIN uCommerce_ProductDefinitionField ppdf ON pp.ProductDefinitionFieldId = ppdf.ProductDefinitionFieldId
        WHERE
            ppdf.Deleted = ''0''

    UNION ALL

        SELECT
                pd.ProductId
            ,    pdpdf.Name AS [PropertyName]
            ,    pdp.Value AS [PropertyValue]
        FROM
            uCommerce_ProductDescription pd
                LEFT JOIN uCommerce_ProductDescriptionProperty pdp ON pd.ProductDescriptionId = pdp.ProductDescriptionId
                LEFT JOIN uCommerce_ProductDefinitionField pdpdf ON pdp.ProductDefinitionFieldId = pdpdf.ProductDefinitionFieldId
        WHERE
            pdpdf.Deleted = ''0''
    ) AS x
    PIVOT 
    (
        MAX([PropertyValue])
        FOR [PropertyName] IN (' + @cols + ')
    ) AS pvt
        LEFT JOIN uCommerce_Product p ON pvt.ProductId = p.ProductId
        LEFT JOIN uCommerce_ProductDefinition def ON p.ProductDefinitionId = def.ProductDefinitionId
        LEFT JOIN uCommerce_ProductDescription pd ON p.ProductId = pd.ProductId
        LEFT JOIN uCommerce_ProductPrice pp ON p.ProductId = pp.ProductId
        LEFT JOIN uCommerce_Price pgp ON pp.PriceId=pgp.PriceId
        INNER JOIN uCommerce_PriceGroup pg ON pgp.PriceGroupId = pg.PriceGroupId
    WHERE
        def.Deleted = ''0''
        AND pg.Deleted = ''0''
    ORDER BY
        p.Sku
        , p.VariantSku
        , p.Name
'

PRINT(@query)
EXECUTE(@query);

Export Ucommerce Category Hierarchy

WITH Cats (CategoryId, ParentCategoryId, CategoryName, CategoryPath, SortOrder)
AS
(
    SELECT
        c.CategoryId
        , c.ParentCategoryId
        , LTRIM(RTRIM(c.Name))
        , CAST(LTRIM(RTRIM(c.Name)) AS NVARCHAR(MAX))
        , CAST(REPLACE(STR(c.SortOrder, 4), SPACE(1), '0') AS VARCHAR(255))
    FROM uCommerce_Category c
    WHERE
        c.Deleted = '0'
        AND c.ParentCategoryId IS NULL
    
    UNION ALL

    SELECT
        ic.CategoryId
        , ic.ParentCategoryId
        , LTRIM(RTRIM(ic.Name))
        , CONCAT(c.[CategoryPath], ' > ', LTRIM(RTRIM(ic.Name)))
        , CAST(CONCAT(c.[SortOrder], '>', REPLACE(STR(ic.SortOrder, 4), SPACE(1), '0')) AS VARCHAR(255))
    FROM 
        uCommerce_Category ic INNER JOIN Cats c ON ic.ParentCategoryId = c.CategoryId
    WHERE
        ic.Deleted = '0'
)
SELECT
    c.CategoryId
    , c.ParentCategoryId
    , c.CategoryName
    , c.CategoryPath
    , STRING_AGG (CAST(cpr.ProductId AS NVARCHAR(MAX)), ', ') AS ProductIds
FROM 
    Cats c
        LEFT JOIN uCommerce_CategoryProductRelation cpr ON c.CategoryId = cpr.CategoryId
GROUP BY
    c.CategoryId
    , c.ParentCategoryId
    , c.CategoryName
    , c.CategoryPath
    , c.SortOrder
ORDER BY
    c.SortOrder