Thread: Help exporting via SQL - Product ID, Name, and Manufacturer....etc

Help exporting via SQL - Product ID, Name, Manufacturer, and all Categories (if possible). I need a query to do this, I'm going to start uploading my product to Ebay I would like to build the categories again same way on my site. Thank you !

You're not being too clear. Here are some queries:

SELECT * FROM Product
SELECT * FROM ProductVariant
SELECT * FROM Category
SELECT * FROM Manufacturer

They will certainly get all the information you have asked for, but they're probably not what you really want.

Ok, what I want, is a query to return me the Product ID, Variant ID, The Manufacturer (of the product), the Category (both parent and subs), and also the Product name and Variant Name, I want those columns all in 1 return

Code:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)
SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer
FROM ProductVariant AS pv WITH (NOLOCK)
JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
WHERE pv.Deleted=0 AND pv.Published=1
AND p.Deleted=0 and pv.Published=1

this is great!, how would I add product type to it and get the product type name, not just the ID ?

Code:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)
SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer, pt.Name
FROM ProductVariant AS pv WITH (NOLOCK)
JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
JOIN ProductType AS pt ON pt.ProductTypeID=p.ProductTypeID
JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
WHERE pv.Deleted=0 AND pv.Published=1
AND p.Deleted=0 and pv.Published=1

In many cases exports like you are making are going to be to an XML API of some kind. Every API has it's own structure of course, but here's an example I put together using esedirect's excellent CTE posted above to produce a basic XML file. This is just an example of one way to do it. The code will have to be modified as necessary to get the desired XML structure.

Code:

     IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
        DROP TABLE #TempTable
     
     CREATE TABLE #TempTable
            (
             [ID] INT IDENTITY(1,1) NOT NULL
            ,[ProductID] INT NULL
            ,[ProductName] VARCHAR(255) NULL
            ,[SKU] VARCHAR(255) NULL
            ,[VariantID] INT NULL
            ,[VariantName] VARCHAR(255) NULL
            ,[Colors] VARCHAR(255) NULL
            ,[Sizes] VARCHAR(255) NULL
            ,[Price] VARCHAR(255) NULL
            ,[CategoryID] INT NULL
            ,[CategoryName] VARCHAR(255) NULL
            ,[ManufacturerID] INT NULL
            ,[ManufacturerName] VARCHAR(255) NULL
            ,PRIMARY KEY (ID)
            ,UNIQUE (ID)
            )
 

    ;WITH Categories(ParentCategoryID,CategoryID,Name)
     AS (
        SELECT
            c.ParentCategoryID
           ,c.CategoryID
           ,c.Name
        FROM
            dbo.Category AS c
        WHERE
            ParentCategoryID = 0
            AND Deleted = 0
            AND Published = 1
        UNION ALL
        SELECT
            c.ParentCategoryID
           ,c.CategoryID
           ,c.Name
        FROM
            dbo.Category AS c
        INNER JOIN 
            Categories AS s
            ON c.ParentCategoryID = s.CategoryID
        WHERE
            Deleted = 0
            AND Published = 1
        )
    INSERT INTO #TempTable
    SELECT 
        p.ProductID
       ,p.Name AS ProductName
       ,p.SKU
       ,pv.VariantID
       ,pv.Name AS VariantName
       ,pv.Colors
       ,pv.Sizes
       ,pv.Price
       ,c.CategoryID
       ,c.Name AS CategoryName
       ,m.ManufacturerID
       ,m.Name AS ManufacturerName
    FROM
        dbo.ProductVariant AS pv WITH (NOLOCK)
    INNER JOIN 
        dbo.Product AS p WITH (NOLOCK)
        ON p.ProductID = pv.ProductID
    INNER JOIN 
        dbo.ProductCategory AS pc WITH (NOLOCK)
        ON pc.ProductID = p.ProductID
    INNER JOIN 
        dbo.ProductManufacturer AS pm WITH (NOLOCK)
        ON pm.ProductID = p.ProductID
    INNER JOIN 
        dbo.Manufacturer AS m WITH (NOLOCK)
        ON pm.ManufacturerID = m.ManufacturerID
    INNER JOIN 
        Categories AS c WITH (NOLOCK)
        ON c.CategoryID = pc.CategoryID
    WHERE
        pv.Deleted = 0
        AND pv.Published = 1
        AND p.Deleted = 0
        AND pv.Published = 1  
    ORDER BY
        ProductID
       ,VariantID
       ,CategoryID
       ,ManufacturerID     
  

    SELECT 
        (
         SELECT
            (
             SELECT
                ISNULL(CAST(ProductID AS VARCHAR(255)),'') AS 'ID'
               ,(
                 SELECT
                    ISNULL(CAST(ProductName AS VARCHAR(255)),'') AS 'Name'
                    FOR XML PATH(''),TYPE
                )   
               ,(
                 SELECT
                    ISNULL(CAST(SKU AS VARCHAR(255)),'') AS 'SKU'
                    FOR XML PATH(''),TYPE
                )
               ,(
                 SELECT
                    ISNULL(CAST(VariantID AS VARCHAR(255)),'') AS 'VariantID'
                   ,ISNULL(CAST(VariantName AS VARCHAR(255)),'') AS 'VariantName'
                   ,ISNULL(CAST(Colors AS VARCHAR(255)),'') AS 'VariantColor'
                   ,ISNULL(CAST(Sizes AS VARCHAR(255)),'') AS 'VariantSize'
                   ,ISNULL(CAST(Price AS VARCHAR(255)),'') AS 'VariantPrice'
                    FOR XML PATH('Variant'),TYPE
                )
               ,(
                 SELECT
                    ISNULL(CAST(CategoryID AS VARCHAR(255)),'') AS 'CategoryID'
                   ,ISNULL(CAST(CategoryName AS VARCHAR(255)),'') AS 'CategoryName'
                    FOR XML PATH('Category'),TYPE
                )
               ,(
                 SELECT
                    ISNULL(CAST(ManufacturerID AS VARCHAR(255)),'') AS 'ManufacturerID'
                   ,ISNULL(CAST(ManufacturerName AS VARCHAR(255)),'') AS 'ManufacturerName'
                 FOR XML PATH('Manufacturer'),TYPE
                )
            FOR XML PATH(''),TYPE
            )
        FOR XML PATH('ProductDetail'),TYPE)
    FROM
        #TempTable
    WHERE
        ID > 0
    FOR XML PATH(''), ROOT('Products')

How could I modify the query below to report inventory as an "In Stock" or "Out Of Stock" at the product level and not the variant level?

For example the query below returns:

SKU STOCK
12345-RED 3
12345-GREEN 1
12345-BLUE 2
54321-BLUE 0
54321-RED 0


I'd rather it just returned one line saying if there were any variants in stock like, without the variant suffixes:

SKU STOCK
12345 IN STOCK
54321 OUT OF STOCK

Originally Posted by esedirect

Code:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)
SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer, pt.Name
FROM ProductVariant AS pv WITH (NOLOCK)
JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
JOIN ProductType AS pt ON pt.ProductTypeID=p.ProductTypeID
JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
WHERE pv.Deleted=0 AND pv.Published=1
AND p.Deleted=0 and pv.Published=1

$25 paypal to the first working answer for my question above.

Don't even get out of bed for that!