SQL Server与Oracle中用存储过程实现按单元格内容行转列

Hey there! Let's figure out how to build a stored procedure that splits the attendance string from your source table into individual characters, then inserts each as a separate row in the target table while keeping the matching ID.

Approach Overview #

The core idea is to generate a sequence of numbers (one for each character position in the attendance string), then use that sequence to extract each character from the string and map it to the original ID.

Example Implementation (SQL Server) #

Here's a stored procedure tailored for SQL Server. We'll use a recursive CTE to generate the number sequence needed to split the string:

CREATE PROCEDURE SplitAttendanceToTarget
AS
BEGIN
    SET NOCOUNT ON;

    -- Optional: Clear existing data in target table (adjust based on your needs)
    -- TRUNCATE TABLE TargetTable;

    -- Recursive CTE to generate number sequence for character positions
    WITH NumberSequence AS (
        SELECT 1 AS Position
        UNION ALL
        SELECT Position + 1
        FROM NumberSequence
        WHERE Position < (SELECT MAX(LEN(attendance)) FROM SourceTable)
    )

    -- Insert split records into target table
    INSERT INTO TargetTable (ID, attendance)
    SELECT 
        s.ID,
        SUBSTRING(s.attendance, ns.Position, 1) AS attendance_char
    FROM SourceTable s
    JOIN NumberSequence ns ON ns.Position <= LEN(s.attendance)
    WHERE s.attendance IS NOT NULL AND s.attendance != ''; -- Skip empty/null values

END;

Example Implementation (MySQL) #

If you're working with MySQL, the approach is similar but uses syntax and functions specific to MySQL:

DELIMITER //

CREATE PROCEDURE SplitAttendanceToTarget()
BEGIN
    -- Optional: Clear target table
    -- TRUNCATE TABLE TargetTable;

    -- Generate number sequence with a loop
    SET @max_len = (SELECT MAX(LENGTH(attendance)) FROM SourceTable);
    SET @pos = 1;

    WHILE @pos <= @max_len DO
        INSERT INTO TargetTable (ID, attendance)
        SELECT 
            ID,
            SUBSTRING(attendance, @pos, 1) AS attendance_char
        FROM SourceTable
        WHERE attendance IS NOT NULL AND LENGTH(attendance) >= @pos;

        SET @pos = @pos + 1;
    END WHILE;

END //

DELIMITER ;

How to Use #

1. Replace SourceTable and TargetTable with your actual table names.
2. Remove the TRUNCATE TABLE line if you don't want to clear existing data in the target table.
3. Execute the procedure:
   • SQL Server: EXEC SplitAttendanceToTarget;
   • MySQL: CALL SplitAttendanceToTarget();

This will take your source data:

And populate the target table exactly as you need:

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