Sunday, 6 March 2016

Find all children for multiple parents in SQL query

CREATE TABLE #tbl_product(
product_id INT IDENTITY(1,1) NOT NULL,
product_name nvarchar(200),
belongs_to INT
)

INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Electronics',NULL)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Furniture',NULL)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('MObile',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Samsung Edge',3)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Lenovo GT',3)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Body Trimmer',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Car Fregnance',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Car Fregnance',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Chair',2)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('NIlkomal Chair',9)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Queen size bed',2)

;WITH cte AS(
SELECT
product_id,
product_name,
belongs_to,
[level] = 0,
Sortid = CAST(product_id AS VARCHAR(MAX))
FROM #tbl_product
WHERE ISNULL(belongs_to,0) = 0
UNION ALL
SELECT
a.product_id,
a.product_name,
a.belongs_to,
[level] = [level] + 1,
Sortid = Sortid + CAST(a.product_id AS VARCHAR(MAX))
FROM #tbl_product a
JOIN cte b
ON a.belongs_to = b.product_id
)
SELECT
product_id,
REPLICATE(' ¦  ', [level]) + product_name,
belongs_to
FROM cte
ORDER BY Sortid


DROP TABLE #tbl_product

No comments:

Post a Comment

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...