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
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