Connecting two tables so that results are shown in multiple columns

Question

Let's assume we have table with data like this

+----------------+------------+-------+
| Company_number |    Date    | Value |
+----------------+------------+-------+
|            123 | 2017-01-01 |     5 |
|            123 | 2017-02-01 |    10 |
|            123 | 2018-01-01 |    15 |
|            456 | 2018-01-05 |    33 |
+----------------+------------+-------+

What should I do to receive data in format

+----------------+------+------------+------------+
| Company_number | Mont | Value 2017 | Value 2018 |
+----------------+------+------------+------------+
|            123 |   01 |          5 |         15 |
|            123 |   02 |         10 |            |
|            456 |   01 |         33 |            |
+----------------+------+------------+------------+

I have no idea how to select all data from 2017 and connect it using the company number and month with data from 2018.

I have taken all of the records from 2017, put it into another table, and tried to use this select, but it doesn't show records when there are no common months (there is no record for February).

select 
s.company_number
,datepart(month,s.date) as Month
,s.Value as Value_2017
,r.Value as Value_2018
from table1 as s
left join table2 as r on concat(r.company_number,datepart(month,r.date))=concat(s.company_number,datepart(month,s.date))
where datepart(year,s.date)='2018'



select results (with no February)
    +----------------+-------+------------+------------+
    | company_number | Month | Value_2017 | Value_2018 |
    +----------------+-------+------------+------------+
    |            123 |     1 |         15 | 5          |
    |            456 |     1 |         33 | NULL       |
    +----------------+-------+------------+------------+

Answer 1

Try this out:

SELECT 
    COALESCE([t1].[company_number], [t2].[company_number]) AS [Company_Number],
    MONTH(COALESCE([t1].[date], [t2].[date])) AS [Month],
    [t1].[value] AS [2018 Value], 
    [t2].[value] AS [2017 Value]
FROM 
    [table1] AS [t1]
FULL OUTER JOIN 
    [table2] as [t2] on [t1].[company_number] = [t2].[company_number] 
    AND MONTH([t1].[date]) = MONTH ([t2].[date])

Just tried it with (can be copy/pasted in editor and executed) and it works fine:

DECLARE @t1 TABLE (Company_number INT, [Date] Date, Value INT)
DECLARE @t2 TABLE (Company_number INT, [Date] Date, Value INT)
INSERT INTO @t1 VALUES (123, '2017-01-01', 5), (123, '2017-02-01', 10)
INSERT INTO @t2 VALUES (123, '2018-01-01', 15), (456, '2018-01-05', 33)

SELECT 
    COALESCE([t1].Company_number, [t2].Company_number) AS [Company_Number],
    MONTH(COALESCE([t1].[date], [t2].[date])) AS [Month],
 [t1].[value] AS [2018 Value], 
    [t2].[value] AS [2017 Value]
FROM 
    @t1 AS [t1]
FULL OUTER JOIN 
    @t2 as [t2] on [t1].[company_number] = [t2].[company_number] 
    AND MONTH([t1].[date]) = MONTH ([t2].[date])