Combining two tables with no commun fields

Cony TN
Cony TN used Ask the Experts™
on
Hi Experts,

I have the followings two tables:

Table1                                        

  Math    | English | History | Chemistry            
---------------------------------------------------
   78                85        91               45
   87                90        82               87
           
 Table2
 
  SID        
-----------  
  201          
  204    
   
 The goal ist to combine both tables so that I have the following table:
 
  | SID   | Course   | Score
 ----------------------------------
     201     Math            78
     201     English         85
     201     History         91
     201     Chemistry     45
     204     Math              87
     204     English          90
     204     History          82
     204     Chemistry     87

With the first table I did an unpivot like this:
SELECT  Course, Score
FROM Table1
UNPIVOT
(    Score
	FOR Course in (Math, English, History, Chemistry)
) AS pt

Open in new window

I do not want to use while loop for the sake of performance.
Does someone have an idea?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Are you saying that row 1 of table1 belongs to row 1 of table2?

In SQL tables are "unordered sets" so unless you have some solid basis to establish order with, in truth you are making a massive assumption that the row numbers can be determined consistently.

Have you shown us EVERY column? is there ANYTHING else in those tables?
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
with t1 as (
  select *
  , row_number() over(order by (select 1)) as rn
  from table1
  )
, t2 as (
  select *
  , row_number() over(order by (select 1)) as rn
  from table2
  )
select
t2.*, t1.*
from t1
inner join t2 on t1.rn = t2.rn
;

Open in new window

Result:
| SID | rn | Math | English | History | Chemistry | rn |
|-----|----|------|---------|---------|-----------|----|
| 201 |  1 |   78 |      85 |      91 |        45 |  1 |
| 204 |  2 |   87 |      90 |      82 |        87 |  2 |

Open in new window


see: http://sqlfiddle.com/#!18/150692/3

NOW unpivot the result

But please note, there really is no absolute guarantee this will be 100% accurate. It will probably work, but you should take steps to avoid such a need in future.

Author

Commented:
the SIDs in table2 are the datasetnumbers. To every row in table1 one should allocate a datasetnumber coming from table2. There are no other columns in those tables.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"the SIDs in table2 are the datasetnumbers."

? ? ? ? ? ?
Can you provide ALL the relevant data please

or:
Perhaps you could try what I have already suggested. Does this work for you?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
What about ....
;with cte_t2 as
( select sid, row_number() over ( order by sid) t2seq
  from Table2
),cte_t1 as
( select Math, English, History, Chemistry , row_number() over ( order by Math, English, History, Chemistry) t1seq 
  from Table1
) select SID, Course, Score
  FROM cte_t1 t1
  inner join cte_t2 t2 on t1seq = t2seq
  UNPIVOT
  ( Score FOR Course in (Math, English, History, Chemistry)) u

Open in new window

You need to create *something* to link them together.... So, used ROW_NUMBER() but could be any other windowed function like count(*) etc....
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@portletpaul

Hahaha *snap* can only agree with your approach :)

@Cony TN

How does dataset number relate to a row of the unpivot ?

Please explain....

My results from the above is
SID         Course                 Score
----------- ---------------------- -----------
201         Math                            78
201         English                         85
201         History                         91
201         Chemistry                       45
204         Math                            87
204         English                         90
204         History                         82
204         Chemistry                       87

(8 rows affected)

Open in new window

Is that not what you wanted ? Seems to match the requirement - if not what is different ?

Author

Commented:
'How does dataset number relate to a row of the unpivot ?'

I have other tables but I am not allowed to give more informations. Thank you for your great help :-)!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial