基于STUFF、UNPIVOT和GROUP BY子句的SQL数据转换需求

Got it, let's tackle this problem step by step. Here's the SQL query that meets your requirement—it will unpivot the columns, group by Id and the unique date, then concatenate the corresponding column names into a comma-separated list:

SELECT 
    Id,
    DisclosureDate,
    STUFF(
        (SELECT ', ' + Column_Name
         FROM UnpivotedData ud
         WHERE ud.Id = main.Id AND ud.DisclosureDate = main.DisclosureDate
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS AssociatedColumns
FROM (
    SELECT 
        Id,
        Column_Name,
        DisclosureDate
    FROM YourTableName
    UNPIVOT (
        DisclosureDate FOR Column_Name IN (
            [Disclosure],
            [Photo Disclosure],
            [Community Trip Disclosure],
            [Assum Of Risk Disclosure],
            [Release Of Info],
            [Photo DisclosureDate]
        )
    ) AS UnpivotResult
) AS UnpivotedData main
GROUP BY Id, DisclosureDate
ORDER BY Id, DisclosureDate;

Let me break down how this works: #

1. UNPIVOT Step: The inner query takes your original table and converts each of the date columns into individual rows. Each row now has the Id, the name of the original column (Column_Name), and its corresponding date (DisclosureDate).
2. Grouping: We group the unpivoted data by Id and DisclosureDate—this ensures we're handling all columns that share the same date for a specific record.
3. String Concatenation: The STUFF function paired with FOR XML PATH('') takes all the Column_Name values in each group and joins them into a single comma-separated string. The STUFF removes the leading , that would otherwise be at the start of the string.

Example Output (using your sample data): #

Just replace YourTableName with the actual name of your table, and you're good to go!

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