Best method to write a recursive SQL view

Question

I am looking for the cleanest SQL query to attain the following. Performance is not as important because my dataset is small.

Sample table:
Letter field holding: A, B, C, D, E,
Location field holding: UAE, CANADA, BOSTON, BAHRAIN, FRANCE

And I am looking for a result that lists every letter/location with letter in it combination, so the following result set:

A-UAE
A-CANADA
A-BAHRAIN
A-FRANCE
B-BOSTON
B-BAHRAIN
C-CANADA
C-FRANCE
D-CANADA
E-UAE
E-FRANCE

Shouldn't C show France? – Adam Jacobson Feb 02 '17 at 20:40 — Adam Jacobson, Feb 02 '17 at 20:40
`Canada` should come with `D` too? – Evaldas Buinauskas Feb 02 '17 at 20:45 — Evaldas Buinauskas, Feb 02 '17 at 20:45

score 2 · Answer 1 · edited May 23 '17 at 12:33

This is yet another solution:

DECLARE @Letter TABLE (
  letter CHAR(1) PRIMARY KEY
  );

DECLARE @Country TABLE (
  name VARCHAR(100) PRIMARY KEY
  );

INSERT INTO @Letter (letter)
VALUES ('A'), ('B'), ('C'), ('D'), ('E');

INSERT INTO @Country (name)
VALUES ('UAE'), ('CANADA'), ('BOSTON'), ('BAHRAIN'), ('FRANCE');

SELECT CONCAT(L.letter, ' - ', C.name)
FROM @Letter AS L
INNER JOIN @Country AS C
  ON C.name LIKE '%' + L.letter + '%'
ORDER BY L.letter, C.name;

Result:

A - BAHRAIN 
A - CANADA  
A - FRANCE  
A - UAE     
B - BAHRAIN 
B - BOSTON  
C - CANADA  
C - FRANCE  
D - CANADA  
E - FRANCE  
E - UAE

Hopefull this outputs what you'd expect.
You can run this query on Stack Exchange Data: https://data.stackexchange.com/stackoverflow/query/622821

Alternatively, if performance becomes issue, you could create a seperate table which would store each country name and its' unique letters, so you could make a simple join instead of LIKEing to compare things:

DECLARE @CountrySplit TABLE (
  letter CHAR(1)
  , name VARCHAR(100)
  , PRIMARY KEY (letter, name)
  );

INSERT INTO @CountrySplit (letter, name)
SELECT DISTINCT SUBSTRING(C.name, v.number + 1, 1), C.name
FROM @Country AS C
INNER JOIN master..spt_values AS V
    ON V.number < LEN(C.name)
WHERE V.type = 'P';

SELECT CONCAT(L.letter, ' - ', CS.name) AS Result
FROM @CountrySplit AS CS
INNER JOIN @Letter AS L
    ON L.letter = CS.letter;

This is query on Stack Exchange Data: https://data.stackexchange.com/stackoverflow/query/622841

Credits to this answer for string split: T-SQL Split Word into characters

John Cappelletti · Answer 2 · 2017-02-02T21:16:33.903

With the help of a Parse/Split UDF and a Cross Apply.

I added an ID to demonstrate that this can be run for the entire table

Example

Declare @YourTable table (ID int,Letter varchar(50),Location varchar(50))
Insert Into @YourTable values
(1,'A, B, C, D, E,','UAE, CANADA, BOSTON, BAHRAIN, FRANCE')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select NewValue = B1.RetVal+'-'+B2.RetVal 
                  From [dbo].[udf-Str-Parse](A.Letter,',') B1
                  Join [dbo].[udf-Str-Parse](A.Location,',') B2
                    on  charindex(B1.RetVal,B2.RetVal)>0
             ) B

Returns

ID  NewValue
1   A-UAE
1   A-CANADA
1   A-BAHRAIN
1   A-FRANCE
1   B-BOSTON
1   B-BAHRAIN
1   C-CANADA
1   C-FRANCE
1   D-CANADA
1   E-UAE
1   E-FRANCE

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

EDIT - Option without a UDF

Declare @YourTable table (ID int,Letter varchar(50),Location varchar(50))
Insert Into @YourTable values
(1,'A, B, C, D, E,','UAE, CANADA, BOSTON, BAHRAIN, FRANCE')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select NewValue = B1.RetVal+'-'+B2.RetVal 
                  From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.Letter,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
                  Join (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.Location,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B2
                    on  charindex(B1.RetVal,B2.RetVal)>0
             ) B

Best method to write a recursive SQL view

2 Answers2