Combining two tables with no commun fields

Hi Experts,

I have the followings two tables:


  Math    | English | History | Chemistry            
   78                85        91               45
   87                90        82               87
 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
(    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
Cony TNAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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
t2.*, t1.*
from t1
inner join t2 on t1.rn = t2.rn

Open in new window

| 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


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cony TNAuthor 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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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

Perhaps you could try what I have already suggested. Does this work for you?
Mark WillsTopic AdvisorCommented:
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
  ( 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 AdvisorCommented:

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 ?
Cony TNAuthor 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 :-)!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.