Tags

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


SQL Puzzle | The XML Path Puzzle

Write a T-SQL that gives us data in the expected format. Basically we have to get first name, middle and last name tilt separated for each Id and lineId

For more please check out the sample input and the expected output.

Sample Inputs

ID LineID F1 F2 F3
1 1 Joe Thomas Smith
1 1 David Harry Jones
1 2 Joe Thomas Smith
1 2 David Hary Jones
1 3 Joe Thomas Smith
1 4 Joe Thomas Smith
1 4 David Harry Jones
1 4 Carlos Jay Smith
2 1 Jane Janet Garcia

Expected Output

ID LineID F1 F2 F3 R
1 1 Joe Thomas Smith Joe~Thomas~Smith| David~Harry~Jones
1 1 David Harry Jones Joe~Thomas~Smith| David~Harry~Jones
1 2 Joe Thomas Smith Joe~Thomas~Smith| David~Hary~Jones
1 2 David Hary Jones Joe~Thomas~Smith| David~Hary~Jones
1 3 Joe Thomas Smith Joe~Thomas~Smith
1 4 Joe Thomas Smith Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1 4 David Harry Jones Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1 4 Carlos Jay Smith Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
2 1 Jane Janet Garcia Jane~Janet~Garcia

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE testPivot
(
	 ID     TINYINT 
	,LineID TINYINT 
	,F1     VARCHAR(25)
	,F2     VARCHAR(25)     
	,F3     VARCHAR(25)
)
GO

INSERT INTO testPivot VALUES
(1,   1,       'Joe'   , 'Thomas',    'Smith'),
(1,   1,       'David' , 'Harry'  ,   'Jones'),
(1,   2,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   2,       'David' , 'Hary'    ,  'Jones'),
(1,   3,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'David' , 'Harry'   ,  'Jones'),
(1,   4,       'Carlos', 'Jay'     ,  'Smith'),
(2,   1,       'Jane',   'Janet'   ,  'Garcia')
GO

--

SOLUTION – 1


--



SELECT m.ID, m.LineID , m.F1 , m.F2 , m.F3 ,  STUFF 
                ((
					SELECT '| ' + m2.N
					FROM (SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot) m2
					WHERE ( m.LineID = m2.LineID AND m.ID = m2.ID )
					FOR XML PATH('')
					) ,1,2,'') 
                R
FROM
(
	SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot
)m

--


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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements