Tags

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


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

Types Parent Child
Shares Q12 Q03
Shares Q13 Q58
Shares Q03 Q81
Shares Q58 NULL
Shares Q81 Q13
Deposit Q85 Q32
Deposit Q32 NULL
Deposit Q21 Q85

Expected Output

Types Parent Child
Deposit Q21 Q85
Deposit Q85 Q32
Deposit Q32 NULL
Shares Q12 Q03
Shares Q03 Q81
Shares Q81 Q13
Shares Q13 Q58
Shares Q58 NULL

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

Advertisements