Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL to transpose table

Posted on 2014-12-10
14
Medium Priority
?
268 Views
Last Modified: 2014-12-11
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
Comment
Question by:SASnewbie
[X]
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
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 59

Assisted Solution

by:HainKurt
HainKurt earned 400 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

Open in new window

0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40492219
0
 

Author Comment

by:SASnewbie
ID: 40492243
Thanks so much! When it works... it's BEAUTIFUL!!
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:SASnewbie
ID: 40492256
Is 'x' set as an integer?
0
 
LVL 59

Expert Comment

by:HainKurt
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 59

Expert Comment

by:HainKurt
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

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

Expert Comment

by:HainKurt
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

by:SASnewbie
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

by:SASnewbie
ID: 40492314
*this way?
0
 
LVL 59

Expert Comment

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

Author Comment

by:SASnewbie
ID: 40492351
Ah, okay.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 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)

Open in new window

The results of that query are:
| FIELD1 | FIELD2 |
|--------|--------|
|    ABC |     20 |
|    ABC |     30 |
|    ABC |     40 |
|    ABC |    999 |
|    DEF |     20 |
|    DEF |     30 |
|    DEF |     40 |
|    DEF |    999 |

Open in new window

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)

Open in new window

0
 

Author Comment

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

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
What we learned in Webroot's webinar on multi-vector protection.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question