Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql server 2008 - How to transpose data into a new table

Posted on 2013-05-14
2
Medium Priority
?
465 Views
Last Modified: 2013-05-15
Hi..
I have an odd table that looks like this

CUST_ID       ORDER1   ORDER2   ORDER3   ORDERDATE1    ORDERDATE2  ORDERDATE3
1         Shoes        Pants        Socks         1/2/2013         1/3/2013         1/4/2013

How can I create an a query that will put it into a new table that looks like this

CUST_ID     ORDER           ORDERDATE
1       Shores               1/2/2013
1       Pants                 1/3/2013
1      Socks                  1/4/2013



thanks
0
Comment
Question by:JElster
2 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 39166631
can you try this.
;with cte1 as (select cust_id,OrdersCol,Orders,row_number() over (partition by cust_id order by OrdersCol) rn 
                 from test 
			  unpivot (Orders for OrdersCol in (Order1,Order2,Order3)) unpvt),
      cte2 as (select cust_id,OrderDateCol,OrderDate,row_number() over (partition by cust_id order by OrderDateCol) rn 
	            from test 
			  unpivot (OrderDate for OrderDateCol in (OrderDate1,OrderDate2,OrderDate3)) unpvt)
select c1.cust_id,c1.Orders,c2.OrderDate
  from cte1 c1
  join cte2 c2 on c1.cust_id = c2.cust_id and c1.rn = c2.rn  

Open in new window

see here: http://sqlfiddle.com/#!3/3f264/7
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 39166884
I think a simpler approach is available, using cross apply:
select
  cust_id
, orderid
, orderdate
from test
cross apply (
        values
                 (ORDER1,ORDERDATE1)
               , (ORDER2,ORDERDATE2)
               , (ORDER3,ORDERDATE3)
             ) as crossapplied (ORDERID, ORDERDATE)  

Open in new window

http://sqlfiddle.com/#!3/3f264/10

nb: you might want to avoid calling a field 'order' as that is a sql reserved word 'orderid' or something along those lines might be a better option.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

572 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