SQL Server 2014中能否将重复城市组合并为地区分组？

Absolutely, you can pull this off in SQL Server 2014! The core idea is to first identify unique city combinations per user, assign a shared region ID to identical combinations, then map back to the original city entries. Here's a practical implementation:

Step 1: Create Test Data (Optional) #

First, let's replicate your sample table to test the query:

CREATE TABLE #UserCities (
    Name VARCHAR(10),
    City VARCHAR(1)
);

INSERT INTO #UserCities (Name, City)
VALUES
('Per 1', 'A'),
('Per 1', 'B'),
('Per 1', 'C'),
('Per 2', 'A'),
('Per 2', 'B'),
('Per 3', 'A'),
('Per 3', 'B'),
('Per 3', 'C'),
('Per 4', 'D'),
('Per 4', 'E'),
('Per 5', 'A'),
('Per 5', 'B');

Step 2: The Query to Generate Regions #

Since SQL Server 2014 doesn't have the STRING_AGG function (that arrived in 2017), we'll use FOR XML PATH to concatenate cities per user into a unique string. Then we'll use DENSE_RANK to assign the same region number to identical city combinations:

WITH UserCityGroups AS (
    SELECT
        Name,
        -- Concatenate cities in sorted order to ensure consistent grouping
        STUFF((
            SELECT ',' + City
            FROM #UserCities uc2
            WHERE uc2.Name = uc1.Name
            ORDER BY City
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS CityCombination
    FROM #UserCities uc1
    GROUP BY Name
),
RegionAssignments AS (
    SELECT
        CityCombination,
        'Reg ' + CAST(DENSE_RANK() OVER (ORDER BY CityCombination) AS VARCHAR(10)) AS Region
    FROM UserCityGroups
    GROUP BY CityCombination
)
SELECT
    ra.Region,
    uc.City
FROM #UserCities uc
JOIN UserCityGroups ucg ON uc.Name = ucg.Name
JOIN RegionAssignments ra ON ucg.CityCombination = ra.CityCombination
ORDER BY ra.Region, uc.City;

How This Works #

• UserCityGroups CTE: For each user, we concatenate their cities into a comma-separated string (sorted alphabetically to make sure "A,B" and "B,A" are treated as the same combination, though your sample data doesn't have this edge case).
• RegionAssignments CTE: We group by the unique city combination strings and use DENSE_RANK to generate a sequential region name (like Reg 1, Reg 2) for each unique combination.
• Final Join: We link back to the original user-city data to map each city to its corresponding region, then sort the results to match your expected output.

Expected Output #

Running this query will give you exactly the result you're looking for:

Region | City
Reg 1  | A
Reg 1  | B
Reg 1  | C
Reg 2  | A
Reg 2  | B
Reg 3  | D
Reg 3  | E

内容的提问来源于stack exchange，提问作者Deepak gupta