在SQL Server中用Pivot或聚合函数实现单列转多列的需求咨询

First, let's tackle the string splitting task since you provided specific sample data, then move on to converting a single column to multiple columns using PIVOT or aggregation functions.

1. Splitting a space-separated string into multiple columns #

Your input string (914-3000-0002 03/14/2018 13:03:10 03/16/2018 13:03:10 26074) needs to be split into 4 columns: task no, start date, end date, and id. Here are two approaches depending on your SQL Server version:

For SQL Server 2022 and later (supports STRING_SPLIT with ordinal) #

The STRING_SPLIT function now has an optional third parameter to return the position of each split value, which makes this straightforward:

DECLARE @inputString NVARCHAR(MAX) = '914-3000-0002 03/14/2018 13:03:10 03/16/2018 13:03:10 26074';

SELECT
    MAX(CASE WHEN ordinal = 1 THEN value END) AS [task no],
    CONCAT(MAX(CASE WHEN ordinal = 2 THEN value END), ' ', MAX(CASE WHEN ordinal = 3 THEN value END)) AS [start date],
    CONCAT(MAX(CASE WHEN ordinal = 4 THEN value END), ' ', MAX(CASE WHEN ordinal = 5 THEN value END)) AS [end date],
    MAX(CASE WHEN ordinal = 6 THEN value END) AS [id]
FROM STRING_SPLIT(@inputString, ' ', 1); -- The 1 enables ordinal output

For older SQL Server versions (pre-2022) #

If STRING_SPLIT doesn't support ordinal, use an XML-based split to get the position of each value:

DECLARE @inputString NVARCHAR(MAX) = '914-3000-0002 03/14/2018 13:03:10 03/16/2018 13:03:10 26074';

WITH SplitData AS (
    SELECT
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (
        SELECT TRY_CAST('<x>' + REPLACE(@inputString, ' ', '</x><x>') + '</x>' AS XML).nodes('/x') AS T(x)
    ) AS Temp
    CROSS APPLY (SELECT T.x.value('.', 'NVARCHAR(MAX)') AS value) AS ValueData
    WHERE value <> '' -- Handle any accidental consecutive spaces
)
SELECT
    MAX(CASE WHEN ordinal = 1 THEN value END) AS [task no],
    CONCAT(MAX(CASE WHEN ordinal = 2 THEN value END), ' ', MAX(CASE WHEN ordinal = 3 THEN value END)) AS [start date],
    CONCAT(MAX(CASE WHEN ordinal = 4 THEN value END), ' ', MAX(CASE WHEN ordinal = 5 THEN value END)) AS [end date],
    MAX(CASE WHEN ordinal = 6 THEN value END) AS [id]
FROM SplitData;

2. Converting a single column to multiple columns (PIVOT or aggregation) #

Let's assume you have a table where each row holds a key-value pair (e.g., a single column of attributes tied to an item). For example, a table SingleColumnData like this:

Using Aggregation + CASE (universal approach) #

This method works in all SQL Server versions and gives you full control:

SELECT
    ItemID,
    MAX(CASE WHEN Category = 'TaskNo' THEN Value END) AS [task no],
    MAX(CASE WHEN Category = 'StartDate' THEN Value END) AS [start date],
    MAX(CASE WHEN Category = 'EndDate' THEN Value END) AS [end date],
    MAX(CASE WHEN Category = 'ID' THEN Value END) AS [id]
FROM SingleColumnData
GROUP BY ItemID;

We use MAX here because each ItemID + Category combination has only one value—you could also use MIN since they're the same.

Using PIVOT (cleaner for static columns) #

If you know the exact column names you want to pivot into, the PIVOT operator is more concise:

SELECT
    ItemID,
    [TaskNo] AS [task no],
    [StartDate] AS [start date],
    [EndDate] AS [end date],
    [ID] AS [id]
FROM SingleColumnData
PIVOT (
    MAX(Value) -- Aggregation function; MAX works since each group has one value
    FOR Category IN ([TaskNo], [StartDate], [EndDate], [ID])
) AS PivotedData;

Note: If your target columns are dynamic (unknown at query time), you'll need to use dynamic SQL to build the PIVOT query dynamically.

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