SQL Server 2005, turn columns into rows.

Question

I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions.

Example: I have a table with the columns:
ID int,
ISO char(2),
Text varchar(255).

So I have this:

ID ISO Text
-- --- ----
 1 DE  Auto
 2 EN  Car

I'd like to get the following:

ID EN  DE
-- --- ----
 1 Car Auto

How do you accomplish that? Thanks.

Answer 1

Since you explicitly asked for a non-pivot solution: this should work, if you know which ISOs you will have in the rows. I called the table "Test".

declare @temp table ([ID] int, [de] varchar(255), [en] varchar(255)) -- add ISOs if necessary

INSERT @temp 
SELECT distinct [ID], '', '' from Test -- ADD '' for other ISOs if necessary

DECLARE c CURSOR read_only 
FOR SELECT [ID], [ISO], [Text] from test

DECLARE  @ID int, @ISO char(2), @Text varchar(255)
OPEN c

FETCH NEXT FROM c INTO @ID, @ISO, @Text
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
     UPDATE  @temp
     SET  [DE] = case when @ISO = 'DE' then @Text else [de] end,
      [EN] = case when @ISO = 'EN' then @Text else [en] end
                    -- add ISOs if necessary
     WHERE   [ID] = @ID
    END
    FETCH NEXT FROM c INTO @ID, @ISO, @Text
END

CLOSE c
DEALLOCATE c

SELECT * FROM @temp

Answer 2

I found the solution as the following:

SELECT ID, DE, EN FROM TextTable PIVOT( MAX([text]) FOR ISO in (DE,EN)) p

It's possible to use PIVOT with MAX aggregating function over the text.

Answer 3

This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).

Basically, you use that solution, with a tweak:

SELECT max(DE) as DE, max(EN) as EN FROM test PIVOT( MAX([text]) FOR ISO in (DE,EN)) p

This will get the content into a single row. Also, it drops the ID, since it doesn't make sense if you want a single row (there is no logic to indicate what to do with it when combining into a single row).

Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAX aggregate.

Answer 4

Query without a PIVOT even though other answers prove you can use a PIVOT :)

SELECT
    MAX(DE.Text) AS DE,
    MAX(EN.Text) AS EN 
FROM TextTable AS TT
LEFT JOIN TextTable AS DE
    ON DE.ID = TT.ID
    AND DE.ISO = 'DE'
LEFT JOIN TextTable AS EN
    ON EN.ID = TT.ID
    AND EN.ISO = 'EN'

Answer 5

This link will solve ur problem www.logiclabz.com/sql-server/transpose-rows-to-columns-in-sql-server-2005-using-pivot.aspx