Solved

SQL to transpose table

Posted on 2014-12-10
14
245 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:HainKurt
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

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40492219
0
 

Author Comment

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

Author Comment

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

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 51

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 51

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 51

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 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now