SQL中如何将多行转置合并为单行并生成伪列头（含去重处理）

Got it, let's work through this problem together. You need to collapse those duplicate rows into a single line per ItemID, with unique LocIDs in custom-named columns (LocID_1 up to LocID_10 max), and you don't want the standard pivot that turns LocID values into headers. Here's a solid solution that hits all your requirements:

Static Solution (Fixed Up to 10 Columns) #

This approach uses a CTE to first deduplicate LocIDs and assign sequential numbers, then uses conditional aggregation to pivot the data into your custom column structure.

WITH Unique_Loc_Assignments AS (
    -- First, remove duplicates and assign a unique row number per ItemID
    SELECT DISTINCT
        ItemID,
        LocID,
        ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY LocID) AS Loc_Sequence
    FROM Your_Table_Name -- Replace with your actual table name
)
SELECT
    ItemID,
    -- Map each sequence number to a custom column
    MAX(CASE WHEN Loc_Sequence = 1 THEN LocID END) AS LocID_1,
    MAX(CASE WHEN Loc_Sequence = 2 THEN LocID END) AS LocID_2,
    MAX(CASE WHEN Loc_Sequence = 3 THEN LocID END) AS LocID_3,
    MAX(CASE WHEN Loc_Sequence = 4 THEN LocID END) AS LocID_4,
    MAX(CASE WHEN Loc_Sequence = 5 THEN LocID END) AS LocID_5,
    MAX(CASE WHEN Loc_Sequence = 6 THEN LocID END) AS LocID_6,
    MAX(CASE WHEN Loc_Sequence = 7 THEN LocID END) AS LocID_7,
    MAX(CASE WHEN Loc_Sequence = 8 THEN LocID END) AS LocID_8,
    MAX(CASE WHEN Loc_Sequence = 9 THEN LocID END) AS LocID_9,
    MAX(CASE WHEN Loc_Sequence = 10 THEN LocID END) AS LocID_10
FROM Unique_Loc_Assignments
GROUP BY ItemID;

How This Works: #

1. Deduplication & Sequencing: The Unique_Loc_Assignments CTE uses DISTINCT to eliminate duplicate LocIDs for each ItemID, then ROW_NUMBER() assigns a sequential number to each unique LocID (sorted alphabetically here—adjust the ORDER BY clause if you need a different sort order, like chronological).
2. Conditional Aggregation: The main query uses MAX(CASE...) to pivot each numbered LocID into its custom column. Since we group by ItemID, the MAX function picks the single valid value for each sequence number, leaving NULL for columns where there's no corresponding LocID (replace MAX with COALESCE(MAX(...), '') if you prefer empty strings instead of NULL).

Dynamic Solution (Auto-Generate Columns Up to 10) #

If you want to avoid hardcoding all 10 columns, this dynamic SQL version builds the column list automatically while sticking to your custom naming rule:

DECLARE @ColumnList NVARCHAR(MAX);
DECLARE @PivotSequenceList NVARCHAR(MAX);
DECLARE @FinalSQL NVARCHAR(MAX);

-- Generate the custom column names (LocID_1 to LocID_10)
SET @ColumnList = STUFF((
    SELECT ',' + QUOTENAME('LocID_' + CAST(n AS VARCHAR(2)))
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS NumberSequence(n)
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Generate the sequence numbers for pivoting
SET @PivotSequenceList = STUFF((
    SELECT ',' + QUOTENAME(n)
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS NumberSequence(n)
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Build the final query
SET @FinalSQL = N'
WITH Unique_Loc_Assignments AS (
    SELECT DISTINCT
        ItemID,
        LocID,
        ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY LocID) AS Loc_Sequence
    FROM Your_Table_Name
)
SELECT
    ItemID,
    ' + @ColumnList + '
FROM Unique_Loc_Assignments
PIVOT (
    MAX(LocID)
    FOR Loc_Sequence IN (' + @PivotSequenceList + ')
) AS PivotedResults;';

-- Execute the dynamic query
EXEC sp_executesql @FinalSQL;

Key Notes: #

• This uses PIVOT but targets the sequence number (not the LocID value itself), so you get your custom LocID_n headers instead of using LocID values as column names.
• Adjust the ORDER BY LocID in the CTE if you need to prioritize LocIDs in a different order (e.g., based on when they were added to the table, if you have a timestamp column).

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