SQL Puzzle | Order By using Parent Child Relationship

In this puzzle you have to write the T-SQL statements that orders the table by Types in descending, then order the Q’s(Events) in which they execute. using the child to reference the parent.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

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

--

CREATE table X1 
(
      Types VARCHAR(50)
     ,Parent VARCHAR(50)
     ,Child VARCHAR(50)
)
GO

INSERT INTO X1 VALUES
      ('Shares','Q12','Q03')
     ,('Shares','Q13','Q58')
     ,('Shares','Q03','Q81')
     ,('Shares','Q58',NULL)
     ,('Shares','Q81','Q13')
     ,('Deposit', 'Q85', 'Q32')
     ,('Deposit', 'Q32', NULL)
     ,('Deposit', 'Q21', 'Q85')
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

--

;WITH CTE AS 
(
	SELECT *, 0 lvl FROM X1 where Child IS NULL
	UNION ALL
	SELECT b.*, a.lvl + 1 
	FROM CTE a INNER JOIN X1 b ON a.Types = b.Types and a.Parent = b.Child
)
SELECT a.Types,a.Parent,a.Child FROM CTE a ORDER BY Types ,lvl desc

--

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

DECLARE @SQL varchar(8000)=”
SELECT @SQL =
@SQL + ‘ALTER TABLE ‘ + s.name+’.’+t.name + ‘ DROP CONSTRAINT [‘ + RTRIM(f.name) +’];’ + CHAR(13)
FROM sys.Tables t
INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = f.schema_id
WHERE t.name LIKE ‘tbl_Carbon’

–EXEC (@SQL)

PRINT @SQL

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com