在SQL Server 2008中合并行

I have a table like the one below:

我有一张像下面这样的桌子:

Customer   |Type     |Count
Joe        |Silver-S |1
Joe        |Silver   |7
Joe        |Gold     |3
Joe        |Gold-S   |2

I need to merge this so that it looks like the following:

我需要合并它，使它看起来如下:

Customer   |Type     |Count
Joe        |Silver   |8
Joe        |Gold     |5

Help!

的帮助!

5 个解决方案

#1

select Customer, [Type], SUM([Count]) from (
    select Customer, replace([Type], '-S', '') [Type], [COUNT] from Customer
)
t
group by customer, [Type]

#2

Here is a solution utilising a partition approach:

以下是一种采用分区方法的解决方案:

SELECT DISTINCT Customer, REPLACE([Type], '-S', '') AS [Type], 
SUM([Count]) OVER (PARTITION BY (SELECT REPLACE([Type], '-S', ''))) AS [Count]
FROM Customer

#3

Try this:

试试这个:

select 
    Customer, 
    left([Type],CHARINDEX('-', [type])), 
    sum(COUNT) 

from Customers

group by 
    Customer, 
    left([Type],CHARINDEX('-', [type]));

Or this: // edit: this may be slower

或者这个://编辑:这个可能比较慢

    select 
        Customer, 
        replace([Type], '-S', ''), 
        sum(COUNT) 

    from Customers

    group by 
        Customer, 
        replace([Type], '-S', '');

If this your database project - you should re-design that database and put that "-S" in another field, or avoid situations like this in the future.

如果这是您的数据库项目——您应该重新设计该数据库并将“- s”放在另一个字段中，或者在将来避免这种情况。

#4

    select Customer, 
case when Type like 'Silver%' then 'Silver' 
when Type like 'Gold%' then 'Gold' end, sum(Count)
    from table
    group by Customer, 
case when Type like 'Silver%' then 'Silver' 
when Type like 'Gold%' then 'Gold' end

#5

I think it is much cleaner to use a cte in this case. I would do it something like this:

我认为在这种情况下使用cte要干净得多。我会这样做:

First the test data:

第一个测试数据:

DECLARE @tbl TABLE(Customer VARCHAR(100),[Type] VARCHAR(10),[Count] INT)

INSERT INTO @tbl
VALUES
    ('Joe','Silver-S',1),
    ('Joe','Silver',7),
    ('Joe','Gold',3),
    ('Joe','Gold-S',2)

The the query:

查询:

;WITH CTE AS
(
    SELECT
        tbl.Customer,
        REPLACE(tbl.[Type],'-S','') AS [Type],
        tbl.[Count]
    FROM
        @tbl AS tbl
)
SELECT
    CTE.Customer,
    CTE.[Type],
    SUM(cte.[Count]) AS [Count]
FROM
    CTE
GROUP BY
    CTE.Customer,
    CTE.[Type]

#1

select Customer, [Type], SUM([Count]) from (
    select Customer, replace([Type], '-S', '') [Type], [COUNT] from Customer
)
t
group by customer, [Type]

#2