Restructuring data from 2 tables into 1 table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Restructuring data from 2 tables into 1 table

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-03-30 : 00:39:37

Hi,

I have 2 tables Table A and Table B which have some data.

The common column between the two tables is “Test_ID” which can have unequal number of entries between them (Example: “Test_ID” - 1107 has only one entry In table A and two entries in Table B )

Data in table A is Split by “Name_ID” with no duplicates per one “Test_ID”. (Example: “Test_ID” -1105 has two “Name_ID”s 111 and 222).

Data in Table B is Split by “Subject_ID” with no duplicates per one “Test_ID”.(Example:”Test_ID”-1105 has two “Subject_ID”s 1 and 2).

Now I need to have the combined data from table A and table B in one table (Table C).Data in Table C must be split up based on “Subject_ID” and “Name_ID” together (there should not be any duplicates entries on combination of both “Subject_ID” and “Name_ID”s ).While splitting the data “Total_Percent” amount can have duplicate entries for different “subject_id”s on combination of “ Name_ID” and “Test_ID”

Table A:

Table B:

Table C:

Hope you guys will help me solve the task. Please reply if you did not understand any part in the problem. Waiting for the responses. Thanks in advance

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-30 : 01:01:29

It would be good if you posted the columns of table A and B... but I think this is what your query would look like to create the table_C...you just need to correct the column where to pull from on the select clause...

select a.Test_ID, b.Subject_ID, a.Name_ID,
b.Full_Name, b.Subject_Name, b.Total_Percent
into Table_C
from table_A a
join table_B b
on(a.test_ID = b.test_ID)
order by 1,2,3,4,5

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-03-30 : 02:09:19

Hi chrianth

i have attached the screen shots above.In case if you cannot see them i am posting the data below

Table A

Test_ID Name_ID Full_Name Total_Percent
-------- ------- ------- ------------
1105 111 AAA 10
1105 222 BBB 90

1106 111 AAA 50
1106 333 CCC 50

1107 333 CCC 100

Table B

Test_ID Subject_ID Subject_Name
------ -------- ------------

1105 1 Geography
1105 2 History

1106 2 History
1106 3 Physics

1107 4 Chemistry
1107 5 Math

Table C

Test_ID Subject_ID Name_ID Full_Name Subject_Name Total_Percent
------- ---------- ------- -------- ----------- --------------
1105 1 111 AAA Geography 10
1105 1 222 BBB Geography 90
1105 2 111 AAA History 10
1105 2 222 BBB History 90

1106 2 111 AAA History 50
1106 2 333 CCC History 50
1106 3 111 AAA Physics 50
1106 3 333 CCC Physics 50

1107 4 333 CCC Chemistry 100
1107 5 333 CCC Math 100

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-30 : 08:07:10

did these work?

select a.Test_ID, b.Subject_ID, a.Name_ID,
a.Full_Name, a.Subject_Name, a.Total_Percent
into Table_C
from table_A a
join table_B b
on(a.test_ID = b.test_ID)
order by 1,2,3,4,5

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-03-30 : 13:47:29

Thanks chrianth it worked fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources