Collapsing Rows into with CONCAT?

Question

I have tried various ways to bring these two rows into one, but cannot seem to find the right method. I have thousands of rows of data to do this for and it would great to find a solution.

So far, I have this code:

SELECT
    CustomerID
    , ServiceCode
    , CONCAT(LABEL_ONE, + ', ' + LABEL_TWO, + ', ' + LABEL_THREE, + ', ' + LABEL_FOUR) Labels
    , SUM(AMOUNT) TotalAmount
FROM Customer.dbo.Orders
GROUP BY
    CustomerID
    , ServiceCode
    , Labels

The results I am getting is this:

CustomerID	ServiceCode	Labels	TotalAmount
123456	1066		172.33
123456	1066	HY, CFD	0

What I need is this:

CustomerID	ServiceCode	Labels	TotalAmount
123456	1066	HY, CFD	172.33

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Jan 12 '23 at 21:21
Maybe as simple as MAX(CONCAT(....)) and remove you column alias from the group by? Also, you should not use string literals as column aliases. It is confusing and it is deprecated. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-deprecated-features-object?view=sql-server-ver16 — Sean Lange, Jan 12 '23 at 21:25
Please include the data that provides your results. Side note, `concat_ws(', '...)` — Stu, Jan 12 '23 at 21:45
Aggregate around your case remove it from your group by. Try Min, Max, StringAgg, etc. — VLOOKUP, Jan 12 '23 at 23:49
@Vlookup - THANK YOU! That did the trick. I just added MAX(CONCAT(etc...)) and remove from GROUP BY and that fixed it. Thank you! — JPSeagull, Jan 13 '23 at 00:37
@JPSeagull I created a formal answer just in case someone else finds this helpful. If it meets your need please mark it off. PS you are most certainly welcome. — VLOOKUP, Jan 20 '23 at 13:34

score 1 · Answer 1 · answered Jan 20 '23 at 13:32

AS discussed in our notes just place a max aggregate around your concate. This should get the grain of your group by set correctly. HTH

SELECT
    CustomerID
    , ServiceCode
    , MAX(CONCAT(LABEL_ONE, + ', ' + LABEL_TWO, + ', ' + LABEL_THREE, + ', ' + LABEL_FOUR)) Labels
    , SUM(AMOUNT) TotalAmount
FROM Customer.dbo.Orders
GROUP BY
    CustomerID
    , ServiceCode

score 0 · Answer 2 · answered Jan 12 '23 at 22:56

If you convert your labels to a column of data grouped on customerID and serviceCode, you can solve this with two intermediate results - one for the amounts grouped by CustomerID and ServiceCode, and one for the labels grouped by CustomerID and ServiceCode. With the labels, you have the added step of converting your "column" of values into a "list" of values, which is something that has been asked many times before I guess. There is more than one solution, and probably better ones if you have more recent versions of SQL Server. See this post: how-to-turn-one-column-of-a-table-into-a-csv-string-in-sql-server-without-using

Here is an example for your situation, but surely needs more data for testing, to make sure all your cases are accounted for. I suppose it goes without saying that you should normalize your data, and not have these fields such as Label_One, Label_Two, Label_Three ... etc., and then we would not need the long union query to get all the labels together.

-- -------------------------------------------------
-- Some Test Data
declare @Temp table (
    CustomerID int,
    ServiceCode int,
    Amount decimal(9, 2),
    Label_One nvarchar(30),
    Label_Two nvarchar(30),
    Label_Three nvarchar(30),
    Label_Four nvarchar(30)
);
insert into @Temp values
    (123456, 1066, 123.66, 'HY', 'CFD', null, null),
    (123456, 1066, 0.00, null, null, null, null)
-- -------------------------------------------------
-- -------------------------------------------------

-- Two CTEs representing the amounts by service code and customer,
-- and the labels by service code and customer
-- (think of these as two temp tables if you are not familiar with CTEs)
;with amounts as (
    select
        CustomerID,
        ServiceCode,
        sum(Amount) TotalAmount
    from
        @Temp
    group by
        CustomerID,
        ServiceCode
),
labels as (
    select
        CustomerID,
        ServiceCode,
        Label_One as [Label]
    from @Temp
    union all
    select
        CustomerID,
        ServiceCode,
        Label_Two as [Label]
    from @Temp
    union all
    select
        CustomerID,
        ServiceCode,
        Label_Three as [Label]
    from @Temp
    union all
    select
        CustomerID,
        ServiceCode,
        Label_Four as [Label]
    from @Temp)

-- join the two CTEs on service code and customer,
-- with a "column to csv list" strategy for the labels.
select 
    amounts.CustomerID,
    amounts.ServiceCode,
    substring(
        (select ',' + labels.[Label]
        from labels labels
        where labels.CustomerID = amounts.CustomerID
            and labels.ServiceCode = amounts.ServiceCode
        order by labels.[Label]
        for xml path('')),2,200000
    ) as CSVList,
    amounts.TotalAmount
from 
    amounts;

Collapsing Rows into with CONCAT?

2 Answers2