Combining Rows of Queried Results by Unique Identifier

Combining rows of queried results by unique identifier?

Given this sample data:

CREATE TABLE #a
(
    Museum     varchar(32),
    MuseumID   int, 
    Country    varchar(32),
    City       varchar(32),
    Paintings  varchar(32),
    Sculptures varchar(32)
);

INSERT #a VALUES
('Louvre',345,'France','Paris', 'Mona Lisa',     NULL),
('Louvre',345,'France','Paris', NULL,            'Venus De Milo'),
('Louvre',345,'France','Paris', 'Ship of Fools', NULL);

In older versions of SQL Server, we'd have to perform grouped string aggregation using a derived table that munged strings together using FOR XML PATH:

SELECT Museum, MuseumID, Country, City, 
    Art = STUFF((SELECT ', ' + COALESCE(Paintings, Sculptures, '')
    FROM #a AS a2
    WHERE a2.museum = a.museum AND a2.MuseumID = a.MuseumID
    AND a2.Country = a.Country AND a2.City = a.City
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1,2,'')
FROM #a AS a
GROUP BY Museum, MuseumID, Country, City;

In SQL Server 2017+, we can use STRING_AGG() for a much simpler and more efficient query:

SELECT Museum, MuseumID, Country, City,
  Art = STRING_AGG(COALESCE(Paintings, Sculptures, ''), ', ')
FROM #a
GROUP BY Museum, MuseumID, Country, City;

Results in both cases:

Museum	MuseumID	Country	City	Art
Louvre	345	France	Paris	Mona Lisa, Venus De Milo, Ship of Fools

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

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.

SQL - How to combine rows based on unique values

Assuming each organ can either have just one value or a null, as shown in the sample data, the max aggregate function should do the trick:

SELECT   id, name, 
         MAX(heart), MAX(brain), MAX(lungs), MAX(kidneys), 
         age
FROM     my_table
GORUP BY id, name, age

Power Query - Merging rows of data based on unique ID

With your data like this:

Unique ID	Data A	Data B	Data C
ABC	123	789	null
ABC	123	null	name2
BCD	234	null	null
BCD	null	null	null
BCD	1234	null	name2
EFG	333	222	name1
EFG	null	222	null
ABC	null	null	null

How do I combine multiple rows with matching ID's into separate columns in SQL?

Your query is close. You need a GROUP BY:

SELECT u.id, u.name, u.username, u.email,
       MAX(CASE WHEN ui.address_type = 'BT' THEN ui.company END) as Billing_Company,
       . . .
FROM p20kx_users u LEFT JOIN
     p20kx_virtuemart_userinfos ui
     ON u.id = ui.virtuemart_user_id
GROUP BY u.id, u.name, u.username, u.email;

I also introduced table aliases, so the query is easier to write and to read. And I removed the ELSE NULL, because that is redundant.

Concatenate many rows into comma-separated list based on unique id

In Oracle you would do this with a LISTAGG function, but for SQL-Server try this question, there's a few different options there:

ListAGG in SQLSERVER

SQL: Combining two query results that have a common column but each has its own specific unique column

In the 1st query add a NULL column for totalFail and in the 2nd query add a NULL column for totalPass.

Use UNION ALL to get all rows and aggregate:

SELECT TrainingID,
       MAX(totalPass) AS totalPass,
       MAX(totalFail) AS totalFail
FROM (
  SELECT TrainingID, totalPass, NULL AS totalFail
  FROM QueryA
  UNION ALL
  SELECT TrainingID, NULL, totalFail
  FROM QueryB
) t
GROUP BY TrainingID

See a simplified demo.

Combining Rows of Queried Results by Unique Identifier