CODEWITHSUNDEEP
sqlt-sql
sasi
sasi

Reputation: 17

Creating a table with data from two different tables

My requirement is to populate a table with person ids, course ids and dates in multiple rows. I have table 1 with the person ids & dates in the rows and the courses as columns. I have table 2 with the course codes with a correlation to the column values in table 1.

Table 1

+----------+------------+------------+------------+------------+------------+------------+------------+
| PersonID |     C1     |     C2     |     C3     |     C4     |     C5     |     C6     |     C7     |
+----------+------------+------------+------------+------------+------------+------------+------------+
|     1780 | 3/05/2016  | 18/04/2016 | 6/12/2017  | 6/12/2018  | 14/12/2015 | 14/12/2015 | 18/04/2016 |
|     1781 | 17/01/2019 | 11/03/2019 | 9/05/2019  | 15/11/2019 | 15/11/2019 | 18/08/2017 | 11/03/2019 |
|     1898 | 19/08/2019 | 30/03/2020 | 19/08/2019 | 31/03/2020 | NULL       | 16/08/2019 | 31/03/2020 |
|     1899 | 23/09/2019 | 29/11/2019 | 8/05/2020  | 23/09/2019 | 18/12/2019 | 9/09/2019  | 9/09/2019  |
+----------+------------+------------+------------+------------+------------+------------+------------+

Table 2

+-----+--------+
| CID | COMPID |
+-----+--------+
| C1  |   1653 |
| C2  |   1151 |
| C3  |   1155 |
| C4  |   6724 |
| C5  |   4016 |
| C6  |   3220 |
| C7  |   1855 |
| C8  |   6757 |
| C9  |   7591 |
| C10 |   6910 |
+-----+--------+

The intention is to populate a table with multiple rows of person id, course id and dates. Something like below.

Output Table

+----------+--------+------------+
| PersonID | CompID |    Date    |
+----------+--------+------------+
|     1780 |   1653 | 3/05/2016  |
|     1780 |   1151 | 18/04/2016 |
|     1780 |   1155 | 6/12/2017  |
|     1780 |   6724 | 6/12/2018  |
|     1780 |   4016 | 14/12/2015 |
|     1780 |   3220 | 14/12/2015 |
|     1780 |   1855 | 18/04/2016 |
|     1781 |   1653 | 17/01/2019 |
|     1781 |   1152 | 11/03/2019 |
|     1781 |   1155 | 9/05/2019  |
|     1781 |   6724 | 15/11/2019 |
|     1781 |   4016 | 15/11/2019 |
|     1781 |   3220 | 18/08/2017 |
|     1781 |   1855 | 11/03/2019 |
+----------+--------+------------+

I'm not an expert in SQL. So, looking out for help. Thanks in advance!!

UPDATE:

Thank you all for your suggestions. I got this working with the below code.

SELECT PersonID, Compliance,b.COMPID, Date_
FROM  
   (SELECT PersonID, C1, c2, c3, c4, c5  ....Cn
   FROM Table1) p
UNPIVOT
   (Date_ FOR Compliance IN  
      ( C1, c2, c3, c4, c5....Cn)
)AS unpvt
 join Table2 b on unpvt.Compliance = b.CID
GO

Sasi

Upvotes: 1

Views: 318

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You should unpivot the data and then join. The most general method uses union all but different databases might have better methods (in particular, lateral joins).

So:

select p.personid, c.compid, p.date
from ((select t1.personid, t1.c1 as date, 'C1' as cid from table1) union all
      (select t1.personid, t1.c2, 'C2' from table1) union all
      (select t1.personid, t1.c3, 'C3' from table1) union all
      . . .
     ) p left join
     table2 t2
     on t2.cid = p.cid;

Note that table1 is in a very poor format. It is better to have multiple rows rather than multiple columns per person.

EDIT:

In SQL Server, I would recommend CROSS APPLY:

select t1.personid, c.compid, v.date
from table1 t1 cross apply)
     (values (c1, 'C1), (c2, 'C2'), . . . 
     ) v(date, cid) join
     table2 t2
     on t2.cid = v.cid;

Upvotes: 2

childofthealgorithm
childofthealgorithm

Reputation: 152

You must learn pivot table,

  1. Table 1 should pivot example : http://www.buraksecer.com/sql-pivot-kullanimi/
  2. And table 1 it was (PersonId,CID,Date).
  3. We now have the records we want, now we will concatenate between the two tables. Example: https://www.w3schools.com/sql/sql_join.asp

I hope this work ! :)

Upvotes: 0

Related Questions