asked on

SQL to transpose table

Hello,

Assistance requested to perform the following.

Transform table A (n columns) to table B using SQL.

Table A:

FIELD1 FIELD2 FIELD3 FIELD4 FIELDn
ABC 20 30 40 ##
DEF 20 30 40 ##

Table B:

FIELD1 FIELD2
ABC 20
ABC 30
ABC 40
ABC ##
DEF 20
DEF 30
DEF 40
DEF ##

Thanks

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

or start reading this: PIVOT & UNPIVOT

http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

SASnewbie

ASKER

Thanks so much! When it works... it's BEAUTIFUL!!

SASnewbie

ASKER

Is 'x' set as an integer?

HainKurt

pivot & unpivot is difficult to master :)

but if you have small number of columns, you can create a view and use that in your applications...

create view v_t_tableA as (... query here... )

then just use this "select * from v_t_tableA"

HainKurt

x is an alias for that big ugly query inside :) I use it to make it easy to follow...

with x as (...any query here...)
select * from x ...

SASnewbie

ASKER

Okay, thanks. So the order x by 1,2 is referring to position?

HainKurt

"order by 1,2" yes they are index number for columns :) nobody should use it like that :)

it should be "order by FIELD1, FIELD2"

when I am lazy, i just use column indexes :) but when it goes into some real app, I use column names not index...

SASnewbie

ASKER

Should it be written this was?

with x as (
select FIELD1 , FIELD2 from tableA
union all
select FIELD1 , FIELD3 AS FIELD2 from tableA
...
union all
select FIELD1 , FIELDN AS FIELD2 from tableA
)
select * from x order by FIELD1 , FIELD2

SASnewbie

ASKER

*this way?

HainKurt

you dont need to add "as FIELD2" in your queries... the names will come from first select...

SASnewbie

ASKER

Ah, okay.

ASKER CERTIFIED SOLUTION