Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
SASnewbie
Asked:
SASnewbie
  • 7
  • 6
2 Solutions
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
0
 
SASnewbieAuthor Commented:
Thanks so much! When it works... it's BEAUTIFUL!!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SASnewbieAuthor Commented:
Is 'x' set as an integer?
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SASnewbieAuthor Commented:
Okay, thanks. So the order x by 1,2 is referring to position?
0
 
HainKurtSr. System AnalystCommented:
"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
 
SASnewbieAuthor Commented:
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
 
SASnewbieAuthor Commented:
*this way?
0
 
HainKurtSr. System AnalystCommented:
you dont need to add "as FIELD2" in your queries... the names will come from first select...
0
 
SASnewbieAuthor Commented:
Ah, okay.
0
 
PortletPaulCommented:
"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
 
SASnewbieAuthor Commented:
Thank you Paul for this method and directing me to the site sqlfiddle.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now