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?

OP will need to perform a split on divs in Table1 @plaidDK. The values E and D don't exist within Table2, so returning that value would result in a NULL in the column div. — Thom A
– Thom A ♦, Commented Feb 19, 2018 at 9:36
@AdrienBrunelat raises a good point. Storing (delimited) lists in a table, especially when it's a JOIN criteria can get messy; and cause performance issues further down the line. If you can consider changing your table structure, I would suggest that would be the best option. — Thom A
– Thom A ♦, Commented Feb 19, 2018 at 10:41

uzi · Accepted Answer · 2018-02-19 13:24:48Z

3

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

edited Feb 19, 2018 at 13:24

answered Feb 19, 2018 at 9:50

uzi

4,1561 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2018-02-19 09:50:06Z

2

As the OP hasn't yet posted a response to the request for what they've tried, I've intentionally left this incomplete. it's good practice to show us what you've tried before; we aren't here to do your work for you.

OP, You'll need to correctly add a WHERE clause to this, add the relevant columns to the SELECT and finish the CROSS APPLY (it only handles up to the first 2 divs at the moment). If you don't understand, please ask:

CREATE TABLE #Table1 (id tinyint,
                      divs varchar(5));

CREATE TABLE #Table2 (id tinyint,
                      div char(1),
                      Table1_id tinyint,
                      report varchar(50));

INSERT INTO #Table1
VALUES (11,'A'),
       (12,'AB'),
       (13,'C'),
       (14,'E'),
       (15,'BDE'),
       (16,'F');

INSERT INTO #Table2
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"');

GO

SELECT *
FROM #Table1 T1
     CROSS APPLY (VALUES(SUBSTRING(T1.divs,1,1)),(SUBSTRING(T1.divs,2,1))) D(div)
     LEFT JOIN #Table2 T2 ON T1.id = T2.Table1_id
                         AND D.div = T2.div;

GO

DROP TABLE #Table1;
DROP TABLE #Table2;

answered Feb 19, 2018 at 9:50

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

3 Comments

Digerkam Over a year ago

Everything looks perfect but it is grabbing unnecesary rows: sqlfiddle.com/#!18/0882f/2 Why does it do that?

Thom A Over a year ago

@Digerkam As i said in my answer: "As the OP hasn't yet posted a response to the request for what they've tried, I've intentionally left this incomplete" and then, later on: "OP, You'll need to correctly add a WHERE clause to this..." . So, you need to add the correct WHERE, and finish the CROSS APPLY. We aren't here to do all the work for you, hence why it's good for you to show us what you've tried. :)

SqlKindaGuy Over a year ago

Just add the extra substrings and filter where div != '' - But i agree with larnu, you should try to write if yourself, you will learn much more, and he has provided you with a good example :)

SqlKindaGuy · Accepted Answer · 2018-02-19 09:52:52Z

2

You can do it like this with a string_split function which is available in 2016

declare @myt table (id int, divs nvarchar(50)
)
insert into @myt

values

(11  ,'A'  ),
(12  ,'AB' ),
(13  ,'C'  ),
(14  ,'E'  ),
(15  ,'BDE'),
(16  ,'F'  )

declare @myt2 table (id int,div nvarchar(50),table1_id int, report nvarchar(50))
insert into @myt2

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 x.id,b.id as Table1_id,[value],report from (
select *,substring(divs,1,1)+','+substring(divs,2,1)+','+substring(divs,3,1)+','+substring(divs,4,1)+','+substring(divs,5,1) as divs2 from @myt
) x
cross apply string_split(divs2,',')
left join @myt2 b on x.id = b.table1_id and b.div = [value]
where value!= ''

edited Feb 19, 2018 at 9:52

answered Feb 19, 2018 at 9:47

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

4 Comments

SqlKindaGuy Over a year ago

@Digerkam Wrong fiddle. Thats for another answer

SqlKindaGuy Over a year ago

@Digerkam here you go: sqlfiddle.com/#!18/0882f/3 - I dont think fiddle is good at handling variable tables. So i made it as a table in fiddle

Digerkam Over a year ago

Well, your answer is fitting to my situation, but I noticed that we are using 2014, so compatibility level is lower than 130, and that causing crash because of "string_split"... Is there any equalivalent for 2014??

SqlKindaGuy Over a year ago

@Digerkam No there isnt. Its only a feature from 2016 and ahead. Then you have to write like Larnu wrote or uzi - You could write some XML splits but I think thats is overkill. I would go with larnu or uzi's solution then

Yogesh Sharma · Accepted Answer · 2018-02-19 11:06:46Z

Other option is to use of recursive cte method which would not limited to only 5 chars

;with cte as
(
    select id, divs, len(divs) pos, 1 start 
    from table1
), cte1 as
(
    select id, divs, pos, start, '' splitdivs from cte
    union all
    select id, divs, pos, start+1, substring(divs, start+1, 1) splitdivs
    from cte1
    where pos > start
)
select c.id TABLE1_id, t2.id TABLE2_id, 
       (case when splitdivs = '' then substring(divs, 1,1) else splitdivs end) div, 
       t2.report 
from cte1 c
left join table2 t2 on t2.TABLE1_id = c.id and 
          t2.div = (case when splitdivs = '' then substring(divs, 1,1) else splitdivs end)
order by 1

Result :

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

hkravitz · Accepted Answer · 2018-02-19 11:15:40Z

Another solution:

disclaimer I didn't see that Uzi already posted a similar solution

    DECLARE @Tbl TABLE (Id int , Divs nvarchar(5))
    INSERT INTO @Tbl (Id, Divs)
    SELECT 11  , 'A'
    UNION ALL 
    SELECT 12  , 'AB'
    UNION ALL 
    SELECT 13  , 'C'
    UNION ALL 
    SELECT 14  , 'E'
    UNION ALL 
    SELECT 15  , 'BDE'
    UNION ALL 
    SELECT 16  , 'F'


    DECLARE @Tbl2 TABLE (Id INT , Div NVARCHAR(1), Table1_Id INT , Report nvarchar(20))
    INSERT INTO @Tbl2 (Id, Div , Table1_Id , Report)
    SELECT 21  , 'A'   , 11        , '"Lorem ipsum 1"'
    UNION ALL
    SELECT 22  , 'B'   , 12        , '"Lorem ipsum 2"'
    UNION ALL
    SELECT 23  , 'C'   , 13        , '"Lorem ipsum 3"'
    UNION ALL
    SELECT 24  , 'A'   , 12        , '"Lorem ipsum 4"'
    UNION ALL
    SELECT 25  , 'B'   , 15        , '"Lorem ipsum 5"'
    UNION ALL
    SELECT 26  , 'F'   , 16        , '"Lorem ipsum 6"'



    ;WITH nums (n)
    AS 
    (
    SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL))  
    FROM (VALUES (0),(0),(0),(0),(0)) a(n)
    )
    , Tbl1 as 
    (
    SELECT Id , SUBSTRING(Divs,n,1) Div
    FROM @Tbl t
    CROSS APPLY 
        (
        VALUES (LEN(Divs))
        ) X(ChrLen)
    CROSS JOIN nums n
    WHERE n <= ChrLen
    )

    SELECT t1.Id Table1_Id , t2.Id Table2_Id ,t1.Div , t2.Report
    FROM Tbl1 T1
    LEFT JOIN @Tbl2  T2
    ON T2 .Div = T1.Div
    AND t1.Id = T2.Table1_Id

Collectives™ on Stack Overflow

Strings in a Column to Select Query Loop in MSSQL

5 Answers 5

Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related