SQL Puzzle | Get data based on Index from a column

In this puzzle you will give a value from FromValue. From that FromValue we need to find out GetCode(code) rows with where you get that FromValue. For more details please check the sample input and expected output.

Sample Input

FromValue GetCode
0.1 A.B
0.1.2 A.B.C
0.1 A.T
0.1 T.K

Expected Output

FromValue GetCode Value
0.1 A.B B
0.1.2 A.B.C B
0.1 A.T T
0.1 T.K K

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE SplitDataByChar ( FromValue varchar(20) ,GetCode varchar(200) ) GO INSERT INTO SplitDataByChar VALUES ('0.1','A.B'), ('0.1.2','A.B.C'), ('0.1','A.T'), ('0.1','T.K') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- DECLARE @Find AS VARCHAR(10) = '1' ;WITH CTE0 AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk FROM SplitDataByChar ) ,CTE AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM ( SELECT *,CAST(''+ REPLACE(FromValue,'.','')+ '' AS XML) po FROM CTE0 )rt CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o ) ,CTE1 AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM ( SELECT *,CAST(''+ REPLACE(GetCode,'.','')+ '' AS XML) po FROM CTE0 )rt CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o ) SELECT s.*,ISNULL(p.value,'') Value FROM SplitDataByChar s LEFT JOIN ( SELECT a.FromValue, a.GetCode , b.value , a.rnk FROM CTE a INNER JOIN CTE1 b ON a.GetCode = b.GetCode AND a.FromValue = b.FromValue AND a.rnk = b.rnk AND a.rnk1 = b.rnk1 WHERE a.value = @Find )p ON p.FromValue = s.FromValue AND p.GetCode = s.GetCode ORDER BY rnk -- ```

Output-1

 ```-- FromValue GetCode Value -------------------- ---------------------- ---------- 0.1 A.B B 0.1.2 A.B.C B 0.1 A.T T 0.1 T.K K (4 rows affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com