CODEWITHSUNDEEP
sqlsql-serversqlite
Q.T
Q.T

Reputation: 98

MS SQL to SQLite syntax

First of all, I'm trying to achieve the following: enter image description here

I first used MS SQL to figure out how I'm Able to align the tags together.

Here is the schema if you would like to recreate the unnormalised table.

CREATE TABLE unnormalized(
  vendor_tag varchar(200),
  vendor_tag_name varchar(200),
  vendor_id int
  );
  
 INSERT INTO unnormalized
VALUES
('5,8,30,24','Burgers,Desserts,Fries,Salads',1),
('5','Burgers',2),
('8,42','Desserts,Mexican',3),
('1,5,30,16','American,Burgers,Fries,Sandwiches',4),
('1,5,30,16','American,Burgers,Fries,Sandwiches',5);

Here is the code for the normalised table

SELECT
    --*
    DISTINCT CAST(tag_id AS INT) as tag_id ,tag_name
FROM unnormalized 
CROSS APPLY 
(
    (SELECT 
        value as tag_id,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
     FROM STRING_SPLIT(vendor_tag,',') 
    ) a1
    INNER JOIN 
    (SELECT 
        value as tag_name,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
     FROM STRING_SPLIT(vendor_tag_name,',') 
     ) a2
    ON a1.rn = a2.rn
) 
ORDER BY tag_id

Now I'm trying to rewrite this code using SQLite. However there are a few differences such as 'CROSS APPLY' and 'STRING_SPLIT' not being in SQLite. I've looked around and I found out that CROSS APPLY is maybe similar to 'CROSS JOIN' in SQLite and maybe using something like this to separate the string at the first comma it finds??

WITH split(vendor_id, vendor_tag, str) AS (
    SELECT vendor_id, '', vendor_tag||',' FROM unnormalized
    UNION ALL SELECT vendor_id,
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split 
    WHERE str
) 

SELECT vendor_id, vendor_tag
FROM split 
WHERE vendor_tag
ORDER BY vendor_id;

Upvotes: 3

Views: 424

Answers (1)

forpas
forpas

Reputation: 164099

In SQLite you can do it with a recursive CTE:

WITH cte AS (
  SELECT 
    vendor_tag, 
    vendor_tag_name,
    SUBSTR(vendor_tag, 1, INSTR(vendor_tag || ',', ',') - 1) col1,
    SUBSTR(vendor_tag_name, 1, INSTR(vendor_tag_name || ',', ',') - 1) col2
  FROM unnormalized 
  UNION ALL 
  SELECT 
    SUBSTR(vendor_tag, LENGTH(col1) + 2), 
    SUBSTR(vendor_tag_name, LENGTH(col2) + 2), 
    SUBSTR(SUBSTR(vendor_tag, LENGTH(col1) + 2), 1, INSTR(SUBSTR(vendor_tag, LENGTH(col1) + 2) || ',', ',') - 1),
    SUBSTR(SUBSTR(vendor_tag_name, LENGTH(col2) + 2), 1, INSTR(SUBSTR(vendor_tag_name, LENGTH(col2) + 2) || ',', ',') - 1)
  FROM cte  
  WHERE LENGTH(vendor_tag) AND LENGTH(vendor_tag_name)
)
SELECT DISTINCT col1 vendor_tag, col2 vendor_tag_name
FROM cte
WHERE NOT (INSTR(col1, ',') OR INSTR(col2, ',')) AND (LENGTH(col1) AND LENGTH(col2))
ORDER BY vendor_tag + 0

See the demo.
Results:

> vendor_tag | vendor_tag_name
> :--------- | :--------------
> 1          | American       
> 5          | Burgers        
> 8          | Desserts       
> 16         | Sandwiches     
> 24         | Salads         
> 30         | Fries          
> 42         | Mexican

Upvotes: 2

Related Questions