December 30, 2013 at 7:31 am
Anyone can help me to come out a query for below ? The objective is to transform entity as shown in expected output.
Child Parent Name
0001 0001 HQ
01000001HQ Accounting Dept
02000001HQ Marketing Dept
03000001HQ HR Dept
0101 0100 Branch North 111
0102 0100 Branch North 112
0201 0200 Branch North 113
0301 0300 Branch North 114
8900 0300 Branch North 115
0387 8900 Sub Branch North 115
Expected output
----------------
Level1 Level2 Level3 Level4 Name
0001 0100 0101 N/A Branch North 111
0001 0100 0102 N/A Branch North 112
0001 0200 0201 N/A Branch North 113
0001 0300 0301 N/A Branch North 114
0001 0300 8900 0387 Sub Branch North 115
December 31, 2013 at 8:06 am
This article will help you to get there.
Flattening Hierarchies
http://sqlmag.com/t-sql/flattening-hierarchies
SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @h TABLE (
Child char(10) NOT NULL PRIMARY KEY,
Parent char(10) NOT NULL,
[Name] varchar(35) NOT NULL
);
INSERT INTO @h
(Child, Parent, Name)
VALUES
('0001', '0001', 'HQ'),
('0100', '0001', 'HQ Accounting Dept'),
('0200', '0001', 'HQ Marketing Dept'),
('0300', '0001', 'HQ HR Dept'),
('0101', '0100', 'Branch North 111'),
('0102', '0100', 'Branch North 112'),
('0201', '0200', 'Branch North 113'),
('0301', '0300', 'Branch North 114'),
('8900', '0300', 'Branch North 115'),
('0387', '8900', 'Sub Branch North 115');
WITH Tree AS
(
SELECT Child, Parent, 1 AS lvl,
CAST(Child AS VARCHAR(900)) COLLATE Latin1_General_BIN2 AS pth,
[Name]
FROM @h
WHERE Child = Parent
UNION ALL
SELECT C.Child, C.Parent, P.lvl + 1,
CAST(P.pth + C.Child AS VARCHAR(900)) COLLATE Latin1_General_BIN2,
C.Name
FROM Tree AS P
JOIN @h AS C
ON C.Parent = P.Child
WHERE
C.Child <> C.Parent
)
SELECT Child, lvl,
ISNULL(NULLIF(SUBSTRING(pth, 1, 10), ''), 'N/A') AS level1,
ISNULL(NULLIF(SUBSTRING(pth, 11, 10), ''), 'N/A') AS level2,
ISNULL(NULLIF(SUBSTRING(pth, 21, 10), ''), 'N/A') AS level3,
ISNULL(NULLIF(SUBSTRING(pth, 31, 10), ''), 'N/A') AS level4,
[Name]
FROM Tree AS T
WHERE
NOT EXISTS (
SELECT *
FROM @h
WHERE [@H].Parent = T.Child
);
GO
/*
Childlvllevel1level2level3level4Name
0301 30001 0300 0301 N/ABranch North 114
8900 30001 0300 8900 N/ABranch North 115
0387 40001 0300 8900 0387 Sub Branch North 115
0201 30001 0200 0201 N/ABranch North 113
0101 30001 0100 0101 N/ABranch North 111
0102 30001 0100 0102 N/ABranch North 112
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply