How to create a new table that combine 2 tables

Question

Hi,

Hi have a problem figuring out the T-SQL script for the following:-

I have 2 tables :-

and I want to combine the 2 tables and generate a new table like this :-

Any help is very much appreciated.

Thanks

Answer 1

Try a query:

select Job_No, Model, Charges, [Hours]
from Job_charges c
unpivot
(
    Charges for [Column Pos] in ([Column 1], [Column 2], [Column 3], [Column 4])
) u
inner join Model_Hours h on h.[Column Pos] = u.[Column Pos]
where Charges > 0
order by Job_No, Charges

Answer 2

Hi @Mun Foong Woo ,

Welcome to Microsoft Q&A!

Please also refer below using Cross Apply:

select c.[Job No], Model, Charges, [Hours]  
 from Job_charges c  
 CROSS APPLY   
 (  
   VALUES   
         ([Column 1],'Column 1')  
        ,([Column 2],'Column 2')  
        ,([Column 3],'Column 3')  
        ,([Column 4],'Column 4')   
		)U(Charges,[Column Pos])  
 inner join Model_Hours h on h.[Column Pos] = u.[Column Pos]  
 where Charges > 0  
 order by [Job No], Charges  

Output:

Job No	Model	Charges	Hours  
Job01	Toyota	10	0.5  
Job01	Nissan	20	0.6  
Job01	BWM	50	0.8  
Job02	Toyota	60	0.5  
Job02	Audi	70	0.7  
Job02	BWM	100	0.8  

If you have many columns and you would not like to list them manually, you could proceed with dynamic statement as below:

DECLARE @SQL NVARCHAR(MAX)  
      
 SELECT @SQL=STUFF((  
         SELECT ',(' +QUOTENAME(COLUMN_NAME)+' ,'''+COLUMN_NAME+''')'  
         FROM INFORMATION_SCHEMA.COLUMNS   
         WHERE TABLE_NAME = 'Job_charges' AND COLUMN_NAME LIKE 'Column%'  
         FOR XML PATH('')  
             ), 1, 1, '')  
  
 SET @SQL= N'select c.[Job No], Model, Charges, [Hours]  
 from Job_charges c  
 CROSS APPLY   
 (  
   VALUES'+@SQL+ ')U(Charges,[Column Pos])  
 inner join Model_Hours h on h.[Column Pos] = u.[Column Pos]  
 where Charges > 0  
 order by [Job No], Charges'  
      
 EXECUTE sp_executesql @SQL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.