Table A contains data for every employee. Table B contains summarised data. I want to join Table A with Table B to get Table C, which retains the structure of Table A.
input table A below:
| office | job |
|---|---|
| B | SM |
| B | SM |
| B | SM |
| B | SM |
| B | SM |
| B | SM |
| B | SM |
| B | M |
| B | M |
| B | C |
| B | C |
| B | C |
| B | C |
| B | SAC |
| B | SAC |
| B | SAC |
| B | AC |
| B | AC |
The above table contains the office location and the role.
input table B below:
| office | jobs | Count |
|---|---|---|
| B | SM | 7 |
| B | M,C | 6 |
| B | SAC,AC | 5 |
We have to combine the job role of count is less than 5. For the position total count is 7(>5) so,we don't combine that with anything but job roles M has to be combined with C to make the count 6(>5) and SAC has to be combined with AC to make the count>5.this is just the context as these tables are already present as input table.
required output Table C below:
| office | job | count |
|---|---|---|
| B | SM | 7 |
| B | SM | 7 |
| B | SM | 7 |
| B | SM | 7 |
| B | SM | 7 |
| B | SM | 7 |
| B | SM | 7 |
| B | M,C | 6 |
| B | M,C | 6 |
| B | M,C | 6 |
| B | M,C | 6 |
| B | M,C | 6 |
| B | M,C | 6 |
| B | SAC, AC | 5 |
| B | SAC, AC | 5 |
| B | SAC, AC | 5 |
| B | SAC, AC | 5 |
| B | SAC, AC | 5 |
Aim is to combine row level data with aggregated data. I tried using like operator but failed.
I tried using split but the jobs(Table B) column can have more than 2-3 roles. I also tried using like operator but failed as substrings match with other strings
This is one of those questions where "solution" probably lands you into more problems than if you don't do it altogether.
with tablea as (
select *
from
(
VALUES (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'SM')
, (N'B', N'M')
, (N'B', N'M')
, (N'B', N'C')
, (N'B', N'C')
, (N'B', N'C')
, (N'B', N'C')
, (N'B', N'SAC')
, (N'B', N'SAC')
, (N'B', N'SAC')
, (N'B', N'AC')
, (N'B', N'AC')
) t (office,job)
)
, tableB as (
select *
from
(
VALUES (N'B', N'SM', 7)
, (N'B', N'M,C', 6)
, (N'B', N'SAC,AC', 5)
) t (office,jobs,Count)
)
select b.office, b.jobs, b.Count
from tableA a
inner join tableB b
ON b.office = a.office
and ',' + b.jobs + ',' like '%,' + a.job + ',%'
Should output what you want,
',' + b.jobs + ',' like '%,' + a.job + ',%' is the key join which matches containing job from tableA in a list of jobs in tableB.