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?

Your question is too vague. Please clarify your example, and use spaces! — Hosam Aly, Jan 09 '09 at 13:34
As interesting the question is, I suggest you describe what you are trying to achieve in the end.. I get the feeling you could solve whatever you're trying to do WITHOUT turning your table into a transposed monster. — Tiberiu Ana, Jan 09 '09 at 13:52
Re-read the question, OP is trying to do a pivot query without a pivot. — Kev, Jan 09 '09 at 14:24
What flavour of SQL are you targetting? SQL2000, 2005, 2008, MySQL, Oracle..? — Kev, Jan 09 '09 at 14:29
Just to explain why I needed this functionality: we have teh table: Texts which has ID, ISO, Text columns. ID and ISO are PK and are unique. When we want to ask the business for the translations we need to create a CSV user-friendly which is: ID, Text(EN), Text(DE). I hope this makes it clear. — frantisek, Jan 16 '09 at 16:38
This not a case of columns into rows but multiple rows in to combined row — rchacko, Aug 27 '14 at 05:44

score 5 · Answer 1 · edited May 23 '17 at 12:13

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.

score 2 · Answer 2 · edited Oct 18 '12 at 20:21

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.

edited Oct 18 '12 at 20:21

Malachi

3,205
4
29
46

answered Jan 09 '09 at 14:53

frantisek

366
1
4
8

score 1 · Answer 3 · answered Jan 09 '09 at 15:14

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'

score 1 · Answer 4 · edited Feb 06 '12 at 20:04

1

If you try this solution and get a syntax error try setting the compatability mode of your database via

ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 90;

This will set the compatability to SQLServer 2005 and the above queries will execute w/o a syntax error.

edited Feb 06 '12 at 20:04

LoveAndCoding

7,857
2
31
55

answered Feb 06 '12 at 20:02

Lehrian

347
1
2
9

score 1 · Answer 5 · edited Feb 24 '12 at 20:41

1

select 
   t.num_claim_no,
   rtrim (xmlagg (xmlelement (e, t.txt_remarks ||'@'|| t.dat_update_date || '  ,  ')).extract ('//text()'), ',') Remarks,
   rtrim (xmlagg (xmlelement (e, t.num_update_no || ' , ')).extract ('//text()'), ',') SrlNo
from 
   gc_clm_gen_info t
   where t.txt_remarks is not null
  group by 
   t.num_claim_no
;

edited Feb 24 '12 at 20:41

Taryn

242,637
56
362
405

answered Feb 20 '12 at 07:46

Deep

11
1

splattne · Answer 6 · 2009-01-09T15:20:33.693

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

SQL Server 2005, turn columns into rows

6 Answers6

Linked