如何将行拆分为列

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或A001或1A1等等...）

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

需要查询帮助

- Gopal

Answer 1

我会按照以下方式进行操作：

• 在这些ID中将A替换为空，并将它们转换为整数
• 编写三个SELECT语句，其中条件是上一步计算出的整数ID % 3分别为1、2、0，以获得每行记录。
• 现在，我们需要连接这三个SELECT语句的结果，如果你使用的是SQL Server 2005，则可以使用ROW_NUMBER()函数，但由于你使用的是SQL Server 2000，因此你需要使用IDENTITY(INT, 1, 1)为这些SELECT语句中的每一行生成行号，并在此值上进行连接。

但由于IDENTITY只能与INTO子句一起在SELECT中使用，因此你最终会得到包含每个列并连接在一起的临时表。

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

- Vikdor

---

@Gopal，那么您将把它们插入带有 IDENTITY 列的中间表中，并对该值执行模数运算，而不是对ID执行模数运算。 - Vikdor