sql - 如何将行拆分为列

Question

使用 SQL Server 2000

表1

ID

A001
A002
A003
A004
A005
A006
A007
....
....
A028

从上表中，我想将 3 行拆分为 3 列，按 id 排序

行显示如下

预期输出

id1 id2 id3

A001 A002 A003
A004 A005 A006
A007 A008 A009
...
...
A025 A026 A027
A028 null null

ID 不固定，id 也可能包含这样的 (01A or A001 or 1A1 or etc....)

表 1 行数不固定，也可能超过 100 行。第 3 列是固定的。如何查询以上条件。

需要查询帮助

Answer 1

我会这样做:

• 用这些 ID 中的任何内容替换 A 并将它们转换为整数
• 编写三个SELECT语句，条件为在上述步骤%3中计算的整数ID分别为1、2、0，以产生每一行的记录。
• 现在，我们需要连接这三个 SELECT 语句的结果，如果您使用的是 SQL Server 2005，那么 ROW_NUMBER() 会很方便，但由于您使用的是 SQL Server 2000，您将使用 IDENTITY(INT, 1, 1) 为这些 SELECT 语句中的每一行生成行号，并在此值上加入 JOIN。

但由于 IDENTITY 只能在 SELECT 中与 INTO 子句一起使用，您最终会得到包含每一列的临时表并连接在他们身上。

SELECT IDENTITY(INT, 1, 1) AS 'RowNum', ID FROM INTO #Row1 Table1
WHERE CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 1

SELECT IDENTITY(INT, 1, 1) AS 'RowNum', ID FROM INTO #Row2 Table1
WHERE CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 2

SELECT IDENTITY(INT, 1, 1) AS 'RowNum', ID FROM INTO #Row3 Table1
WHERE CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 0

SELECT 
    r1.ID id1,
    r2.ID id2,
    r3.ID id3
FROM
    #Row1 r1
    FULL OUTER JOIN #Row2 r2
    ON r1.RowNum = r2.RowNum
    FULL OUTER JOIN #Row3 r3
    ON r3.RowNum = r3.RowNum

DROP TABLE #Row1
DROP TABLE #Row2
DROP TABLE #Row3

如果您使用的是 SQL Server 2005 或更高版本，所有这些都可以在一个查询中完成，如下所示:

SELECT 
    R1.ID,
    R2.ID,
    R3.ID
FROM 
    (
        SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNum, ID 
        FROM ATABLE 
        WHERE 
            CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 1
    ) AS R1
    FULL OUTER JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNum, ID 
        FROM ATABLE 
        WHERE 
            CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 2
    ) AS R2
    ON R1.RowNum = R2.RowNum
    FULL OUTER JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNum, ID 
        FROM ATABLE 
        WHERE 
            CONVERT(INT, REPLACE(ID, 'A', '')) % 3 = 0
   ) AS R3
    ON R2.RowNum = R3.RowNum