SQL Server：将透视后字符串列转换为datetime列（无临时表）

Got it, let's work through this. You need to pivot your table to turn dtin and dtout (from your parameter column, I assume) into separate columns, pull their values from newValue, and convert those string values to datetime—all without using temp tables. Here are two reliable, straightforward methods:

Method 1: Conditional Aggregation (Flexible & Easy to Adjust) #

This approach uses CASE statements to target the specific parameters, aggregates the matching values, and converts them directly in the same query.

Assuming your original table has a grouping key (like a unique record ID), a column storing parameter names (e.g., paramName), and date strings in newValue, the query would look like this:

SELECT
    -- Replace with your actual grouping column(s) (e.g., OrderID, EmployeeID)
    RecordID,
    -- Convert dtin string to datetime
    CAST(MAX(CASE WHEN paramName = 'dtin' THEN newValue END) AS DATETIME) AS dtin,
    -- Convert dtout string to datetime
    CAST(MAX(CASE WHEN paramName = 'dtout' THEN newValue END) AS DATETIME) AS dtout
FROM YourOriginalTable
-- Group by all non-aggregated columns
GROUP BY RecordID

Quick notes: #

• Use MAX (or MIN) here because we just need to pick the single value for each parameter per group—this works as long as each group has only one dtin and one dtout entry.
• If your date strings use a non-standard format, swap CAST for CONVERT with a style code (e.g., CONVERT(DATETIME, newValue, 120) for yyyy-mm-dd hh:mi:ss).
• If you have multiple grouping columns, add all of them to both the SELECT and GROUP BY clauses.

Method 2: SQL Server PIVOT Operator #

If you prefer using the native PIVOT feature, you can handle the pivot in a subquery, then convert the resulting columns to datetime in the outer select:

SELECT
    -- Replace with your actual grouping column(s)
    RecordID,
    CAST(dtin AS DATETIME) AS dtin,
    CAST(dtout AS DATETIME) AS dtout
FROM (
    -- Source subquery: pull only the columns needed for pivoting
    SELECT RecordID, paramName, newValue
    FROM YourOriginalTable
) AS SourceData
PIVOT (
    -- Aggregate the newValue to get the single value per parameter
    MAX(newValue)
    -- Define which parameter names become columns
    FOR paramName IN ([dtin], [dtout])
) AS PivotTable

Quick notes: #

• Just like the first method, MAX ensures we get the correct single value per parameter group.
• To avoid conversion errors from bad date strings, use TRY_CAST instead of CAST—this returns NULL for invalid entries instead of failing the entire query:

  TRY_CAST(dtin AS DATETIME) AS dtin
  

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