Tags
Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, find records not ending with s, find records not ending with s puzzle, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL puzzle, SQL Puzzle | Split String Puzzle - II (Linked In), SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL server filter index with LIKE and RIGHT function?, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL Server Puzzle, SQL SERVER Puzzles, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ Replace 6 or more Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Tricky Puzzles, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Week puzzle, What You Can (and Can't) Do With Filtered Indexes
SQL Puzzle | Split String Puzzle – II (Linked In)
Puzzle Statement
This puzzle was asked in social media ( Linked In ), In this puzzle you have to split a string and insert in the same column. Basically here the number will be increased based on the id column.
SQL Puzzle – Linked In
Check out the input and output required for this puzzle is-
Sample Input
| 1 | a,b,c |
| 2 | c,d |
| 3 | e,f |
| 4 | x |
Expected output
| ID | SplittedString |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | c |
| 2 | d |
| 3 | e |
| 3 | f |
| 4 | x |
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
Script
Use the below script to generate the source table and fill them up with the sample data.
UPDATE – 6-Oct-2015 – Solution #1
--
SELECT ID, SplittedString FROM
(
SELECT ID, CAST('<A>'+ REPLACE(Value,',','</A><A>')+ '</A>' AS XML) Xmlcol FROM SplitString2
) s
CROSS apply
(
SELECT ProjectData.D.value('.', 'VARCHAR(5)') as SplittedString
FROM s.xmlcol.nodes('A') as ProjectData(D)
)a
--
|
Add a comment if you have any other solution in mind. We all need to learn. Enjoy !!!
Keep Learning
Pawan Khowal
Http://MSBISkills.com
Improving my SQL BI Skills 
Pingback: SQL Server String Puzzles & Tricks | Improving my SQL BI Skills
;WITH CTE1
AS
(
SELECT Emp_ID
,Emp_Name
,SUBSTRING(Emp_Name,1,IIF(CHARINDEX(‘,’,Emp_Name)=0,1,CHARINDEX(‘,’,Emp_Name)-1)) AS [char]
,SUBSTRING(Emp_Name,CHARINDEX(‘,’,Emp_Name)+1,LEN(Emp_Name)) AS [next_Emp_Name]
FROM #ABC
UNION ALL
SELECT Emp_ID
,Emp_Name
,SUBSTRING(next_Emp_Name,1,CHARINDEX(‘,’,next_Emp_Name)-1) AS [char]
,SUBSTRING([next_Emp_Name],CHARINDEX(‘,’,[next_Emp_Name])+1,LEN([next_Emp_Name])) AS [next_Emp_Name]
FROM CTE1
WHERE CHARINDEX(‘,’,next_Emp_Name)>0
UNION ALL
SELECT Emp_ID
,Emp_Name
,SUBSTRING([next_Emp_Name],1,LEN([next_Emp_Name])) AS [char]
,”AS[next_Emp_Name]
FROM CTE1
WHERE CHARINDEX(‘,’,next_Emp_Name)=0 AND CHARINDEX(‘,’,Emp_Name)>0 AND LEN(next_Emp_Name)0
)
SELECT Emp_ID,char
FROM CTE1
Order By Emp_ID
LikeLike