Solved

SQL to transpose table

Posted on 2014-12-10
14
250 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
  • 7
  • 6
14 Comments
 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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

Open in new window

0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 40492219
0
 

Author Comment

by:SASnewbie
ID: 40492243
Thanks so much! When it works... it's BEAUTIFUL!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

by:Huseyin KAHRAMAN
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

by:Huseyin KAHRAMAN
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 51

Expert Comment

by:Huseyin KAHRAMAN
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 51

Expert Comment

by:Huseyin KAHRAMAN
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 48

Accepted Solution

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

679 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