## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# SQL to transpose table

Posted on 2014-12-10
249 Views
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
0
Question by:SASnewbie
• 7
• 6

LVL 51

Assisted Solution

HainKurt earned 100 total points
ID: 40492216
what an ugly query :) but it works...

``````with x as (
select FIELD1 , FIELD2 from tableA
union all
select FIELD1 , FIELD3 from tableA
...
union all
select FIELD1 , FIELDN from tableA
)
select * from x order by 1,2
``````
0

LVL 51

Expert Comment

ID: 40492219
0

Author Comment

ID: 40492243
Thanks so much! When it works... it's BEAUTIFUL!!
0

Author Comment

ID: 40492256
Is 'x' set as an integer?
0

LVL 51

Expert Comment

ID: 40492257
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"
0

LVL 51

Expert Comment

ID: 40492266
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 ...
0

Author Comment

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

LVL 51

Expert Comment

ID: 40492287
"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...
0

Author Comment

ID: 40492308
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
0

Author Comment

ID: 40492314
*this way?
0

LVL 51

Expert Comment

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

Author Comment

ID: 40492351
Ah, okay.
0

LVL 48

Accepted Solution

PortletPaul earned 400 total points
ID: 40493262
"you dont need to add "as FIELD2" in your queries... the names will come from first select..."

That is true, you do not NEED to, but it is good practice as it documents the intention clearly, plus if the query is amended for any reason the intended alignment is retained.

For your original question there is another approach using CROSS APPLY and VALUES. I like this approach as it is very efficient and you get a visual layout the required "unpivot" arrangement.
``````select
tablea.field1
, ca.field2
from tableA
cross apply (

values
(field2)
, (field3)
, (field4)
, (fieldn)

) as CA (field2)
``````
The results of that query are:
``````| FIELD1 | FIELD2 |
|--------|--------|
|    ABC |     20 |
|    ABC |     30 |
|    ABC |     40 |
|    ABC |    999 |
|    DEF |     20 |
|    DEF |     30 |
|    DEF |     40 |
|    DEF |    999 |
``````
See it working at: http://sqlfiddle.com/#!3/d18186/1

Note that the data placed into the field2 column must be compatible data types or converted into compatible data types. This approach allows that too, simply include any required casts/converts into the value area of the cross apply e.g.
``````cross apply (

values
( cast(field2 as varchar(10)) )
, ( cast(field3 as varchar(10)) )
, ( cast(field4 as varchar(10)) )
, ( cast(fieldn as varchar(10)) )

) as CA (field2)
``````
0

Author Comment

ID: 40493605
Thank you Paul for this method and directing me to the site sqlfiddle.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.