I have a dataset in SQL with some tables which I need to join them. There is a main table called ‘main_table’ that consists two parameters (id and name) and one of them is the key ‘id’. main_table, TableA, TableB almost have similar structures, but TableC is a bit different as follows:
main_table: id, name, 01, bb, 02, cc, 03, dd, 04, ff, 05, gg, TableA: id, val, 01, ab, 03, ac, 05, ad, TableB: id, val, 01, ba, 02, bc, 04, bd, TableC: id, val,index 01, cc, 1 01, cdf,2 01, cba,3 03, ggg,1 03, dfg,2
I could join the first three tables with the following query:
SELECT main_table.[id],main_table.[name] ,TableA.val,TableB.val FROM main_table LEFT JOIN TableA ON TableA.id = main_table.id LEFT JOIN TableB ON TableB.id = main_table.id
Which gives me the below table:
result_table: id, name, val_TA, val_TB 01, bb , ab , ba 02, cc , NULL, bc 03, dd , ac , NULL 04, ff , NULL, bd 05, gg , ad , NULL
But, when I want to ‘LEFT JOIN’ the third table (TableC) in this way, it gives me redundant rows, since there are multiple similar ids in TAbleC. I need a query that handles this case and gives me one row for each id when I join them, something like this:
My_desired_table: id, name, val_TA, val_TB,val_TC_index1,val_TC_index2,Val_TC_index3 01, bb , ab , ba, cc , cdf , cba 02, cc , NULL, bc, NULL , NULL , NULL 03, dd , ac , NULL, ggg , dfg , NULL 04, ff , NULL, bd, NULL , NULL , NULL 05, gg , ad , NULL, NULL , NULL , NULL
Advertisement
Answer
There are a couple of ways to achieve this, but perhaps the simplest is as Dale K suggested to PIVOT
on tablec before joining to it. To give you an idea how this would work, something along the lines of:
declare @main_table table (id varchar(5), name varchar(5)); declare @tablea table(id varchar(5), val varchar(5)); declare @tableb table(id varchar(5), val varchar(5)); declare @tablec table(id varchar(5), val varchar(5), idx int); INSERT INTO @main_table VALUES ('01','bb'), ('02','cc'), ('03','dd'), ('04','ff'), ('05','gg'); INSERT INTO @tablea VALUES ('01','ab'), ('03','ac'), ('05','ad'); INSERT INTO @tableb VALUES ('01','ba'), ('02','bc'), ('04','bd'); INSERT INTO @tablec VALUES ('01','cc',1), ('01','cdf',2), ('01','cba',3), ('03','ggg',1), ('03','dfg',2); with cte as ( SELECT id, [1], [2], [3], [4] FROM @tablec c PIVOT ( min(val) FOR idx IN ([1], [2], [3], [4]) ) pvt ) SELECT m.id, m.name, a.val AS val_TA, b.val AS val_TB, c.[1] AS val_TC_index1, c.[2] AS val_TC_index2, c.[3] AS val_TC_index3, c.[4] AS val_TC_index4 FROM @main_table m LEFT JOIN @tablea a on a.id = m.id LEFT JOIN @tableb b on b.id = m.id LEFT JOIN cte c on c.id = m.id;
I have given a maximum of 4 index columns, but it is easy to see how to extend. If you need this to be open ended, then you will need to convert this into dynamic SQL.
Please note (because you are relatively new) that I have done the hard work for you (providing statements to create the tables and insert the data). In the future when asking such questions, please either do something similar or use db fiddle. You will get many more people willing to help you, if they don’t have to do so much typing. Please remember that people are giving you their time for free.