CODEWITHSUNDEEP
sqlsql-server
Ray
Ray

Reputation: 82

SQL Server: Create Comma Separated List of Multiple Categories.. Use STUFF? Something else?

I have a situation where I need to pull existing products out of a MSSQL database to CSV for ingestion into another database, each row will need to contain as much information as possible from the existing database. I think i've gotten a majority of what I need with my query so far, but I am stuck on figuring out how to merge the multiple categories per item down into one row.

Whats happening is i'll have a duplicate row for each category listed. So if its assigned to the category Glass and Glass and Glass Connectors, i'll have a row for each.

I'd like there to be a single field named Categories that's just comma separated like this: "Glass,Glass and Glass Connectors"

I read that STUFF() can do this, but I can't seem to get the syntax right. Other examples on Stack didn't seem to work for my situation or I just don't know exactly how to apply it to my query, the mass amount of JOINs needed hasn't helped either.

Here's my query:

SELECT
    tblCatalog_SKUs.InternalSKU,
    tblCatalog_Products.Name AS ParentProd,
    tblCatalog_Categories.Name AS Category,
    tblCatalog_SKUs_Images.Image1,
    tblCatalog_SKUs_Images.Image2,
    tblCatalog_SKUs_Images.Image3,
    tblCatalog_Products.Summary,
    tblCatalog_SKUs.Name AS optName,
    tblCatalog_SKUs.Description AS optDesc,
    tblCatalog_SKUs.Price,
    tblCatalog_SKUs.Inventory,
    tblCatalog_SKUs.Sale
FROM tblCatalog_Products_Categories
    INNER JOIN tblCatalog_Categories
        ON tblCatalog_Products_Categories.CategoryID = tblCatalog_Categories.CategoryID
    INNER JOIN tblCatalog_SKUs
        ON tblCatalog_Products_Categories.ProductID = tblCatalog_SKUs.ProductID
    INNER JOIN tblCatalog_SKUs_Images
        ON tblCatalog_SKUs.SKUID = tblCatalog_SKUs_Images.SKUID
    INNER JOIN tblCatalog_Products
        ON tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID

Sample results: https://i.sstatic.net/Y7AIt.png

I was hoping there might be something like group_concat in MySQL.

Thanks for your help!

Upvotes: 0

Views: 587

Answers (1)

Dave Manning
Dave Manning

Reputation: 820

You can do this with STUFF and FOR XML PATH like this:

SELECT
    tblCatalog_SKUs.InternalSKU,
    tblCatalog_Products.Name AS ParentProd,
    STUFF((
        SELECT ',' + Name 
        FROM tblCatalog_Categories
        INNER JOIN tblCatalog_Products_Categories ON tblCatalog_Products_Categories.CategoryID = tblCatalog_Categories.CategoryID
        WHERE tblCatalog_Products_Categories.ProductID = tblCatalog_SKUs.ProductID
        FOR XML PATH('')
    ), 1, 1, '') AS Category,
    tblCatalog_SKUs_Images.Image1,
    tblCatalog_SKUs_Images.Image2,
    tblCatalog_SKUs_Images.Image3,
    tblCatalog_Products.Summary,
    tblCatalog_SKUs.Name AS optName,
    tblCatalog_SKUs.Description AS optDesc,
    tblCatalog_SKUs.Price,
    tblCatalog_SKUs.Inventory,
    tblCatalog_SKUs.Sale
FROM tblCatalog_SKUs
    INNER JOIN tblCatalog_SKUs_Images
        ON tblCatalog_SKUs.SKUID = tblCatalog_SKUs_Images.SKUID
    INNER JOIN tblCatalog_Products
        ON tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID

STUFF is just used to remove the leading ',', key part is FOR XML PATH concatenating the strings.

Upvotes: 1

Related Questions