Strings in a Column to Select Query Loop in MSSQL

Question

I have column which contain chars:

TABLE1
======
id  | divs
----------
11  | A
12  | AB
13  | C
14  | E
15  | BDE
16  | F

Every char represents different division, second Table is:

TABLE2
======
id  | div | TABLE1_id | report
------------------------------
21  | A   | 11        | "Lorem ipsum 1"
22  | B   | 12        | "Lorem ipsum 2"
23  | C   | 13        | "Lorem ipsum 3"
24  | A   | 12        | "Lorem ipsum 4"
25  | B   | 15        | "Lorem ipsum 5"
26  | F   | 16        | "Lorem ipsum 6"

And final report is:

Reports
=======
TABLE1_id | TABLE2_id | div | report
------------------------------------
11        | 21        | A   | "Lorem ipsum 1"
12        | 24        | A   | "Lorem ipsum 4"
12        | 22        | B   | "Lorem ipsum 2"
13        | 23        | C   | "Lorem ipsum 3"
14        | NULL      | E   | NULL
15        | 25        | B   | "Lorem ipsum 5"
15        | NULL      | D   | NULL
15        | NULL      | E   | NULL
16        | 26        | F   | "Lorem ipsum 6"

Char count is limited with 5: "ABCDE",

I've tried many SQL queries, but obviously I am missing something, and dont know some important commands for this...

How to generate that report in MSSQL?

Answer 1

Normally you would need a tally table. Since your string is limited you can use numbers generated by yourself

declare @t1 table (
    id  int
    , divs varchar(5)
)
insert into @t1
values 
    (11, 'A')
    ,(12, 'AB')
    ,(13, 'C')
    ,(14, 'E')
    ,(15, 'BDE')
    ,(16, 'F')

declare @t2 table (
    id int
    , div varchar(5)
    , TABLE1_id int
    , report varchar(200)
)

insert into @t2
values
(21, 'A', 11, '"Lorem ipsum 1"')
,(22, 'B', 12, '"Lorem ipsum 2"')
,(23, 'C', 13, '"Lorem ipsum 3"')
,(24, 'A', 12, '"Lorem ipsum 4"')
,(25, 'B', 15, '"Lorem ipsum 5"')
,(26, 'F', 16, '"Lorem ipsum 6"')

select
    t.id, z.id, substring(t.divs, q.n, 1), z.report
from
    @t1 t
    join (values (1), (2), (3), (4), (5)) q(n) on len(t.divs) >= q.n
    left join @t2 z on substring(t.divs, q.n, 1) = z.div and t.id = z.TABLE1_id