SQL脚本：如何按固定行数将单列数据拆分为多列？

Alright, let's work through this problem step by step. You’ve got a single column of weather data packed with three days’ worth of info—each day has 10 distinct attributes—and you want to reshape it so each day becomes a column, with each row holding the same type of attribute (like date, weather condition, temperature, etc.).

First, let’s confirm the structure of your raw data to make sure we parse it correctly:
Each day’s dataset has exactly 10 attributes, even though some values include spaces (like Light rain or 5-10 mm):

1. Date (e.g., FriApr 13)
2. Weather condition (e.g., Light rain)
3. High temperature (e.g., 4°C)
4. Low temperature (e.g., 1)
5. Wind chill (e.g., 3°)
6. Humidity (e.g., 80%)
7. Precipitation range (e.g., 5-10 mm)
8. Wind speed (e.g., 16 km/h)
9. Wind direction (e.g., E)
10. UV index (e.g., 1)

We’ll use PostgreSQL for this example (it has strong regex support), but the logic can be adapted to MySQL, SQL Server, or other databases with string manipulation functions.

Step 1: Setup Sample Data #

First, let’s assume your raw data is stored in a table weather_data with a column raw_text:

CREATE TABLE weather_data (raw_text TEXT);
INSERT INTO weather_data VALUES ('FriApr 13 Light rain 4°C 1 3° 80% 5-10 mm - 16 km/h E 1 SatApr 14 Mixed precipitation 3°C -1 -2° 90% 25-35 mm - 26 km/h NE 0 SunApr 15 Freezing rain 2°C -4 2° 80% 20-30 mm - 37 km/h NE 0');

Step 2: Split Data into Day Blocks #

First, we’ll split the single string into separate blocks for each day using regex to match the start of each date (FriApr, SatApr, SunApr) and capture all content until the next date or the end of the string:

WITH day_blocks AS (
  SELECT
    (regexp_match(raw_text, '(FriApr \d+ .*?)(?= SatApr| SunApr|$)'))[1] AS day1,
    (regexp_match(raw_text, '(SatApr \d+ .*?)(?= SunApr|$)'))[1] AS day2,
    (regexp_match(raw_text, '(SunApr \d+ .*)'))[1] AS day3
  FROM weather_data
)

Step 3: Extract Attributes with Indexes #

Next, we’ll pull out each of the 10 attributes from each day’s block, assigning an index to each attribute type so we can align them across days. We use regex substring matching to handle values with spaces:

, attribute_rows AS (
  -- 1. Date
  SELECT 1 AS attr_idx, day1 AS val1, day2 AS val2, day3 AS val3 FROM day_blocks
  UNION ALL
  -- 2. Weather Condition
  SELECT 2,
    substring(day1 FROM 'FriApr \d+ (.*?) \d°C'),
    substring(day2 FROM 'SatApr \d+ (.*?) \d°C'),
    substring(day3 FROM 'SunApr \d+ (.*?) \d°C')
  FROM day_blocks
  UNION ALL
  -- 3. High Temperature
  SELECT 3,
    substring(day1 FROM '(\d°C)'),
    substring(day2 FROM '(\d°C)'),
    substring(day3 FROM '(\d°C)')
  FROM day_blocks
  UNION ALL
  -- 4. Low Temperature
  SELECT 4,
    substring(day1 FROM '\d°C (-?\d+)'),
    substring(day2 FROM '\d°C (-?\d+)'),
    substring(day3 FROM '\d°C (-?\d+)')
  FROM day_blocks
  UNION ALL
  -- 5. Wind Chill
  SELECT 5,
    substring(day1 FROM '-?\d+ (-?\d°)'),
    substring(day2 FROM '-?\d+ (-?\d°)'),
    substring(day3 FROM '-?\d+ (-?\d°)')
  FROM day_blocks
  UNION ALL
  -- 6. Humidity
  SELECT 6,
    substring(day1 FROM '-?\d° (\d+)%'),
    substring(day2 FROM '-?\d° (\d+)%'),
    substring(day3 FROM '-?\d° (\d+)%')
  FROM day_blocks
  UNION ALL
  -- 7. Precipitation
  SELECT 7,
    substring(day1 FROM '\d+% (\d+-\d+ mm)'),
    substring(day2 FROM '\d+% (\d+-\d+ mm)'),
    substring(day3 FROM '\d+% (\d+-\d+ mm)')
  FROM day_blocks
  UNION ALL
  -- 8. Wind Speed
  SELECT 8,
    substring(day1 FROM 'mm - (\d+ km/h)'),
    substring(day2 FROM 'mm - (\d+ km/h)'),
    substring(day3 FROM 'mm - (\d+ km/h)')
  FROM day_blocks
  UNION ALL
  -- 9. Wind Direction
  SELECT 9,
    substring(day1 FROM 'km/h ([A-Z]+)'),
    substring(day2 FROM 'km/h ([A-Z]+)'),
    substring(day3 FROM 'km/h ([A-Z]+)')
  FROM day_blocks
  UNION ALL
  -- 10. UV Index
  SELECT 10,
    substring(day1 FROM '[A-Z]+ (\d)'),
    substring(day2 FROM '[A-Z]+ (\d)'),
    substring(day3 FROM '[A-Z]+ (\d)')
  FROM day_blocks
)

Step 4: Generate Your Desired Output #

Finally, we’ll format the data into the pipe-separated rows you requested. Use concat_ws to join values with |, and order by the attribute index to keep rows aligned:

SELECT concat_ws(' | ', val1, val2, val3) AS output_line
FROM attribute_rows
ORDER BY attr_idx;

Sample Output #

This query will produce exactly the format you showed:

FriApr 13 | SatApr 14 | SunApr 15
Light rain | Mixed precipitation | Freezing rain
4°C | 3°C | 2°C
1 | -1 | -4
3° | -2° | 2°
80% | 90% | 80%
5-10 mm | 25-35 mm | 20-30 mm
16 km/h | 26 km/h | 37 km/h
E | NE | NE
1 | 0 | 0

Adaptations for Other Databases #

• MySQL 8.0+: Replace regexp_match and substring with REGEXP_SUBSTR and adjust regex patterns slightly.
• SQL Server: Use STRING_SPLIT (with ordinal support in 2022+) or a custom split function, and PATINDEX for pattern matching to extract attributes.

The core idea is leveraging known markers in the data (like °C, %, mm) to reliably extract values, even when they contain spaces.

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