How to split the rows into column

Question

Using SQL Server 2000

Table1

ID

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

From the above table, i want to split the 3 rows into 3 column, order by id

Rows display like this

Expected Output

id1 id2 id3

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

ID is not fixed, id may contain like this also (01A or A001 or 1A1 or etc....)

Table 1 row count is not fixed, it may goes more than 100 rows also. Column 3 is fixed. How to make a query for the above condition.

Need Query Help

Answer 1

I would do it as follows:

• Replace A with nothing in those IDs and convert them to integers
• Write three SELECT statements where the condition will be integer ID calculated in above step %3 is 1, 2, 0 respectively, to yield the records in each row.
• Now, we need to JOIN the results of these three SELECT statements and if you were on SQL Server 2005, then ROW_NUMBER() would have been handy, but since you are on SQL Server 2000, you would use IDENTITY(INT, 1, 1) to generate row numbers for each row in these SELECT statements and JOIN on this value.

But since IDENTITY can be used in SELECT only with INTO clause, you would end up with temporary tables that contain each column and join on them.

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

If you were on SQL Server 2005 or higher, all this could have been done in a single query as follows:

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