Connect two tables so that the results appear in multiple columns

Question

Suppose we have a 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 to 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 the data from 2017 and connect it using the company number and the month with the data from 2018.

I took all the records from 2017, put them in another table and tried to use this selection, but it does not show the records when there are no common months (in February there is no record).

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 the following:

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 using (you can copy / paste into the editor and execute), 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])

Answer 2

CTE 1 2017 2018 .

With tmp as (Select company_id,

part (year, dte) , part (, ) , () tbl by company_id, part (year, dte), part (, )) coalesce (m.company_id, n.company_id) company_id, (., .) , m.value value_2017, n.value value_2018 From ( * from tmp = 2017) ( * tmp = 2018) n m.company_id = n.company_id m.month = n.month

Result:
   company_number   month   Value_2017  Value_2018
    123             1        5                    5
    123             2       10          (null)
    456             1    (null)             23