Merge Multiple Rows Based On Unique Identifier in SQL Server (GROUP_CONCAT for SQL Server)?

Question

I was initially trying to figure this out via PHP, however I have not had much luck...

How Can I Merge All Duplicates In Array Based On One Key's Value?

Since I have not found a solution, I decided to try to solve my issue via my SQL query. What I need to know is how can I "merge" the differences between the returned rows in this query?

SELECT
  Item.ID,
  Item.ItemLookupCode,
  nitroasl_pamtable.ManufacturerPartNumber,
  SupplierList.ReorderNumber,
  Item.Notes,
  Item.Description,
  Item.ExtendedDescription,
  Item.Quantity,
  nitroasl_pamtable.SpoofStock,
  Item.Price,
  nitroasl_pamtable.PAM_Keywords
FROM 
   Item
JOIN 
   nitroasl_pamtable ON Item.ID = nitroasl_pamtable.ItemID
JOIN 
   SupplierList ON Item.ID = SupplierList.ItemID
WHERE 
   (Item.ItemLookupCode LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (nitroasl_pamtable.ManufacturerPartNumber LIKE '%tp-ac1750%' 
    AND Price > 0.00 AND WebItem = 1)
   OR 
   (SupplierList.ReorderNumber LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
   OR 
   (Item.Notes LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (Item.Description LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (Item.ExtendedDescription LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
   OR 
   (nitroasl_pamtable.PAM_Keywords LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
ORDER BY 
    Item.ItemLookupCode ASC;

What I think I need (but haven't successfully implemented)

MySQL's GROUP_CONCAT equivalent

I believe this function would do what I need, but I am using SQL Server - not MySQL. And I cannot seem to get the posted solutions on how to do this to work for me...

What I've tried:

Recently, I tried the MAX() and the GROUP BY function (together), but it picks the MAX value that is returned in the duplicate rows, thus returning a single row with the MAX values in each column.

SELECT
    MAX(Item.ID) AS Id,
    Item.ItemLookupCode,
    MAX(nitroasl_pamtable.ManufacturerPartNumber) AS ManufacturerPartNumber,
    MAX(SupplierList.ReorderNumber) AS ReorderNumber,
    MAX( CAST(Item.Notes AS varchar(max)) ) AS Notes,
    MAX(Item.Description) AS Description,
    MAX( CAST(Item.ExtendedDescription AS varchar(max)) ) AS ExtendedDescription,
    MAX(Item.Quantity) AS Quantity,
    MAX(nitroasl_pamtable.SpoofStock) AS SpoofStock,
    MAX(Item.Price) AS Price,
    MAX(nitroasl_pamtable.PAM_Keywords) AS PAM_Keywords,
    MAX(Item.PictureName) AS PictureName
FROM 
    Item
LEFT JOIN 
    nitroasl_pamtable ON Item.ID = nitroasl_pamtable.ItemID
LEFT JOIN 
    SupplierList ON Item.ID = SupplierList.ItemID
WHERE 
    (Item.ItemLookupCode LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.ManufacturerPartNumber LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (SupplierList.ReorderNumber LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.Notes LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.Description LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.ExtendedDescription LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.PAM_Keywords LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
GROUP BY 
    Item.ItemLookupCode
ORDER BY 
    Item.ItemLookupCode ASC

Instead of discarding the variants of each column, I would like put all the returned values of each column (that are discarded with MAX) into their respective/original columns separated by a comma...

What I need:

Database Schema (Sample)

In the file above, you will see the four rows returned by the above SQL query. I would like to have one row returned that looks like this:

ID:

ItemLookupCode:

TP-AC1750

ManufacturerPartNumber:

Archer C7

ReorderNumber:

7681617, ARCHERC7, N82E16833704177

Notes:

TP-LINK Archer C7 AC1750 Routr

Description:

TP-LINK Archer C7 AC1750 Routr

ExtendedDescription:

TP-Link Archer C7 Wireless-AC1750 Dual-Band Gigabit Router

Quantity:

0 (This should actually be a combined sum/total of the values in this column)

SpoofStock:

NULL (Same as Quantity - Should be sum / This value is different than Quantity)

Price:

129.95

PAM_Keywords:

NULL

I know there is a better way to write this query. I am just not an SQL guy. This query/script is a keyword search that returns items in our Microsoft Dynamics RMS database, and outputs JSON that I use to create a list of products that can be changed and resubmitted to the DB. I use SQL Server 2008 R2 (if that matters). Any advice on how I can accomplish the above output using some variation of my query would be greatly appreciated! Thanks

Update (SQLFiddle)

Here is a link to an SQLFiddle to play around with :)

SQLFiddle with No MAX Function

SQLFiddle with MAX Function (Not a viable solution as I lose data)

ErikE · Accepted Answer

This will get you started, but there is still some uncertainty around the nitroasl_pamtable table, so I didn't include that.

SELECT
  I.ID,
  I.ItemLookupCode,
  I.Notes,
  I.Description,
  I.ExtendedDescription,
  I.Quantity,
  I.Price,
  SL.ReorderNumbers,
  P.SpoofStock,
  P.ManufacturerPartNumber,
  P.PAM_Keywords
FROM
  Item I
  LEFT JOIN nitroasl_pamtable P
    ON I.ID = P.ItemID
  OUTER APPLY (
    SELECT
      ReorderNumbers = Substring((
        SELECT DISTINCT Convert(varchar(max), ', ' + SL.ReorderNumber)
        FROM SupplierList SL
        WHERE I.ID = SL.ItemID
        FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(max)'), 3, 2147483647)
  ) SL
WHERE
  I.Price > 0.00
  AND I.WebItem = 1
  AND (
    I.ItemLookupCode LIKE '%tp-ac1750%'
    OR I.Notes LIKE '%tp-ac1750%'
    OR I.Description LIKE '%tp-ac1750%'
    OR I.ExtendedDescription LIKE '%tp-ac1750%'
    OR P.ManufacturerPartNumber LIKE '%tp-ac1750%'
    OR P.PAM_Keywords LIKE '%tp-ac1750%'
    OR EXISTS (
      SELECT *
      FROM dbo.SupplierList SL2
      WHERE
        I.ID = SL2.ItemID
        AND SL2.ReorderNumber LIKE '%tp-ac1750%'
    )
  )
ORDER BY
  I.ItemLookupCode ASC;

To bring in nitroasl_pamtable correctly, for every column you want to concatenate, you could do a new OUTER APPLY. You can do a single OUTER APPLY to get at once all columns that need normal aggregation (such as Sum()).

However, I would like to offer that this concatenation will obscure the data in a way that could lead to incorrect assessment or decisions. Pulling in 3 values from a table and concatenating/summing them will make them appear to be a single unit, which may not be correct.

Another way that the concatenating may be harmful is with the reorder numbers. Notice that I put a DISTINCT in there because of the two duplicate reorder numbers--but they were from different suppliers. So what good is a reorder number apart from the supplier it can be sourced from? What if two different items have the same reorder number at different suppliers? (E.g., reorder number BIGBOX is a TV at one supplier, but it's a giant cardboard box at the other one.)

I am not convinced that it is a good idea to concatenate these values in the query. Instead, the UI should be presented the queries separately (the Items as one rowset, then the supporting data from each other table as individual rowsets) and then present the data in a way that makes sense in the UI.

Merge Multiple Rows Based On Unique Identifier in SQL Server (GROUP_CONCAT for SQL Server)?

What I think I need (but haven't successfully implemented)

What I've tried:

What I need:

Update (SQLFiddle)

Answers (2)

Related Questions