Import Flat file then Transpose rows to columns

Question

I have a flat text file with several thousand rows that i want to import into a table then transpose the rows to columns. The data in the rows are related so long as an "ID" matches the "ID" of the previous record until the next ID = 0 or InfoObject = "YOO_CH056" then start all over with a new row. The transposed rows can vary from one or two columns called "nodes" up to a maximum of 7 "nodes" also, Ids 1 and 2 are consistent columns for every row. (see sample of expected resulted for a better understanding).

Here is a sample of the Flat Text File:

Function	ID	InfoObject	Node Name	Link ID Parent ID	Child ID	Next ID
Function	1	0HIER_NODE	FUNCTION	0	2	0
Global Functions	2	0HIER_NODE	GF000001	1	3	0
Finance	1331	0HIER_NODE	GF110000	2	1332	1487
Corporate Finance	1333	0HIER_NODE	GF110500	1331	1334	1407
Finance Investment & Other	1372	0HIER_NODE	GF110840	1333	1373	1384
Finance Investment Management	1373	0HIER_NODE	GF110855	1372	1374	1379
Finance Global Real Estate IM	1375	0HIER_NODE	GF110865	1373	1376	1378
AMG/FSD Controllers GRE New	1377	YOO_CH056	GF110875	1375	0	0
Chief Investments Office	1652	0HIER_NODE	GF219000	2	1653	1852
Investments - AMG	1653	0HIER_NODE	GF066400	1652	1654	1783
AIG Investments Other	1753	0HIER_NODE	GF067130	1653	1754	1755
Investments Other	1754	YOO_CH056	GF067150	1753	0	0

Here is the expected result:

Node0

Node0_Name

Node1

Node1_Name

Node2

Node2_Name

Node3

Node3_Name

Node4

Node4_Name

Node5

Node5_Name

Node6

Node6_Name

Node7

Node7_Name

FUNCTION

Function

GF000001

Global Functions

GF219000

Chief Investments Office

GF066400

Investments - AMG

GF067130

AIG Investments Other

GF067150

Investments Other

FUNCTION

Function

GF000001

Global Functions

GF110000

Finance

GF110500

Corporate Finance

GF110840

Finance Investment & Other

GF110855

Finance Investment Management

GF110865

Finance Global Real Estate IM

GF110875

AMG/FSD Controllers GRE New

Thanks in advance,

I'm wrecking my brain.

Mario Scheeren · Answer

Hi, searching for something like this ? DECLARE @tbl table( [Function] Varchar(50), [ID] int, [InfoObject] varchar(20), [Node_Name] varchar(20), [Parent_ID] int, [Child_ID] int, [Next_ID] int) INSERT INTO @tbl ([Function], ID, InfoObject, Node_Name, Parent_ID, Child_ID, Next_ID ) VALUES ('Function', 1, '0HIER_NODE', 'FUNCTION', 0, 2, 0), ('Global Functions',2 ,'0HIER_NODE','GF000001', 1, 3, 0), ('Finance', 1331 ,'0HIER_NODE','GF110000', 2, 1332, 1487), ('Corporate Finance', 1333 ,'0HIER_NODE','GF110500', 1331, 1334, 1407), ('Finance Investment & Other', 1372 ,'0HIER_NODE','GF110840', 1333, 1373, 1384), ('Finance Investment Management', 1373 ,'0HIER_NODE','GF110855', 1372, 1374, 1379), ('Finance Global Real Estate IM', 1375 ,'0HIER_NODE','GF110865', 1373, 1376, 1378), ('AMG/FSD Controllers GRE New', 1377 ,'YOO_CH056','GF110875', 1375, 0, 0), ('Chief Investments Office', 1652 ,'0HIER_NODE','GF219000', 2, 1653, 1852), ('Investments - AMG', 1653 ,'0HIER_NODE','GF066400', 1652, 1654, 1783), ('AIG Investments Other', 1753 ,'0HIER_NODE','GF067130', 1653, 1754, 1755), ('Investments Other', 1754 ,'YOO_CH056','GF067150', 1753, 0, 0); SELECT a.Node_Name AS [Node0], a.[Function] AS [Node0_Name], b.Node_Name AS [Node1], b.[Function] AS [Node1_Name], c.Node_Name AS [Node2], c.[Function] AS [Node2_Name], d.Node_Name AS [Node3], d.[Function] AS [Node3_Name], e.Node_Name AS [Node4], e.[Function] AS [Node4_Name], f.Node_Name AS [Node5], f.[Function] AS [Node5_Name], g.Node_Name AS [Node6], g.[Function] AS [Node6_Name], h.Node_Name AS [Node7], h.[Function] AS [Node7_Name] FROM @tbl a LEFT OUTER JOIN @tbl b ON a.ID = b.Parent_ID LEFT OUTER JOIN @tbl c ON b.ID = c.Parent_ID LEFT OUTER JOIN @tbl d ON c.ID = d.Parent_ID LEFT OUTER JOIN @tbl e ON d.ID = e.Parent_ID LEFT OUTER JOIN @tbl f ON e.ID = f.Parent_ID LEFT OUTER JOIN @tbl g ON f.ID = g.Parent_ID LEFT OUTER JOIN @tbl h ON h.ID = g.Parent_ID WHERE a.Parent_ID = 0

Stan Light · Answer

Thank you Mario. Worked perfectly - I just made one change on the last JOIN