User Tools

Site Tools


notes:sql:techniques_tabletranspose

Table Transpose

We are using the following table as a test input:

CREATE TABLE InputTable (id INT, name VARCHAR(5))

The Table Transpose technique groups rows from the InputTable by id and then generates output. Each row in the output has unique id. The values from the 'name' column in the InputTable are aggregated in the output as a single value in the column 'names'.

Example:

InputTable   output (the temp table #transposed)
id name      id names
-- ----      -- ----- 
1  A         1  A/B/E
1  B         2  A/W
2  W         3  D
3  D
1  E
2  A
CREATE PROC TransposeTable
AS
    CREATE TABLE #transposed (id INT, names VARCHAR(50))
 
    DECLARE @id INT, @name VARCHAR(5)
    DECLARE @oldid INT, @names VARCHAR(50)
 
    -- Records have to be sorted by id
    DECLARE cur CURSOR FOR SELECT id, name FROM InputTable ORDER BY id
 
    OPEN cur FETCH NEXT FROM cur INTO @id, @name
 
    -- Are there any rows?
    IF @@FETCH_STATUS=0
    BEGIN
        -- Initialize variables from the first row
	    SET @oldid = @id
        SET @names = @name
 
        WHILE @@FETCH_STATUS=0
        BEGIN
            -- Get the next row
            FETCH NEXT FROM cur INTO @id, @name
 
            -- Are there any rows?
            IF @@FETCH_STATUS=0
            BEGIN
                IF @oldid<>@id
                BEGIN
                    INSERT INTO #transposed VALUES (@oldid, @names)
                    SET @names = @name
                    SET @oldid = @id
                END 
                ELSE
                BEGIN
                    SET @names = @names + '/' + @name
                END
            END
        END
 
        -- Process the last row
        INSERT INTO #transposed VALUES (@oldid, @names)
    END
 
    CLOSE cur
    DEALLOCATE cur
 
    SELECT * FROM #transposed
 
    DROP TABLE #transposed
GO

Examples of usage.

Example #1:

DELETE FROM InputTable
INSERT INTO InputTable VALUES(1, 'A')
INSERT INTO InputTable VALUES(1, 'B')
INSERT INTO InputTable VALUES(2, 'W')
INSERT INTO InputTable VALUES(3, 'D')
INSERT INTO InputTable VALUES(1, 'E')
INSERT INTO InputTable VALUES(2, 'A')
EXEC TransposeTable
id          names
----------- ----------
1           A/B/E
2           A/W
3           D

Example #2:

DELETE FROM InputTable
INSERT INTO InputTable VALUES(1, 'A')
INSERT INTO InputTable VALUES(1, 'B')
INSERT INTO InputTable VALUES(1, 'C')
EXEC TransposeTable
id          names
----------- ----------
1           A/B/C

Example #3:

DELETE FROM InputTable
INSERT INTO InputTable VALUES(1, 'A')
INSERT INTO InputTable VALUES(2, 'B')
INSERT INTO InputTable VALUES(2, 'C')
EXEC TransposeTable
id          names
----------- ----------
1           A
2           B/C

Example #4:

DELETE FROM InputTable
INSERT INTO InputTable VALUES(1, 'A')
INSERT INTO InputTable VALUES(1, 'B')
INSERT INTO InputTable VALUES(2, 'C')
EXEC TransposeTable
id          names
----------- ----------
1           A/B
2           C

Example #5:

DELETE FROM InputTable
INSERT INTO InputTable VALUES(1, 'A')
INSERT INTO InputTable VALUES(2, 'B')
INSERT INTO InputTable VALUES(3, 'C')
EXEC TransposeTable
id          names
----------- ----------
1           A
2           B
3           C
notes/sql/techniques_tabletranspose.txt · Last modified: 2015/06/24 by admin