Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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('<A>'+ REPLACE(FromValue,'.','</A><A>')+ '</A>' 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('<A>'+ REPLACE(GetCode,'.','</A><A>')+ '</A>' 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