SQL Server: Hierarchical data

Question

I have multiple tables with table names as TableA (Primary Key column: ID1), TableB (Primary Key column: ID2), TableC (Primary Key column: ID3).

CREATE TABLE TableA(
   ID1         INTEGER  NOT NULL PRIMARY KEY 
  ,TableA_Name VARCHAR(14) NOT NULL
  ,TableA_Desc VARCHAR(27) NOT NULL
);

INSERT INTO TableA(ID1,TableA_Name,TableA_Desc) VALUES (1,'TableA Parent1','This is TableA description1');
INSERT INTO TableA(ID1,TableA_Name,TableA_Desc) VALUES (2,'TableA Parent2','This is TableA description2');
INSERT INTO TableA(ID1,TableA_Name,TableA_Desc) VALUES (3,'TableA Parent3','This is TableA description3');

CREATE TABLE TableB(
   ID2         INTEGER  NOT NULL PRIMARY KEY 
  ,TableB_Name VARCHAR(11) NOT NULL
  ,TableB_Desc VARCHAR(27) NOT NULL
);

INSERT INTO TableB(ID2,TableB_Name,TableB_Desc) VALUES (1,'TableB Kid1','This is TableB description1');
INSERT INTO TableB(ID2,TableB_Name,TableB_Desc) VALUES (2,'TableB Kid2','This is TableB description2');
INSERT INTO TableB(ID2,TableB_Name,TableB_Desc) VALUES (3,'TableB Kid3','This is TableB description3');

CREATE TABLE TableC(
   ID3         INTEGER  NOT NULL PRIMARY KEY 
  ,TableC_Name VARCHAR(11) NOT NULL
  ,TableC_Desc VARCHAR(27) NOT NULL
);

INSERT INTO TableC(ID3,TableC_Name,TableC_Desc) VALUES (1,'TableC kid1','This is TableC description1');
INSERT INTO TableC(ID3,TableC_Name,TableC_Desc) VALUES (2,'TableC kid2','This is TableC description2');
INSERT INTO TableC(ID3,TableC_Name,TableC_Desc) VALUES (3,'TableC Kid3','This is TableC description3');
INSERT INTO TableC(ID3,TableC_Name,TableC_Desc) VALUES (4,'TableC Kid3','This is TableC description4');

I have hierarchy table named Hierarchy_Table.

CREATE TABLE Hierarchy_Table(
   Table_Name         VARCHAR(6) NOT NULL PRIMARY KEY
  ,PK_Column          VARCHAR(3) NOT NULL
  ,Selected_ID        INTEGER  NOT NULL
  ,Hierarchy_Level    INTEGER  NOT NULL
  ,Parent_Table       VARCHAR(6)
  ,Parent_Column      VARCHAR(4)
  ,Selected_Parent_ID INTEGER 
);


INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableA','ID1',1,1,NULL,NULL,NULL);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableB','ID2',1,2,'TableA','ID1',1);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableC','ID3',1,3,'TableB','ID2',1);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableC','ID3',2,3,'TableB','ID2',1);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableB','ID2',2,2,'TableA','ID1',1);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableC','ID3',3,3,'TableB','ID2',2);
INSERT INTO Hierarchy_Table(Table_Name,PK_Column,Selected_ID,Hierarchy_Level,Parent_Table,Parent_Column,Selected_Parent_ID) VALUES ('TableC','ID3',4,3,'TableB','ID2',2);

I need to loop through each row for above Hierarchy_Table results set, based on Table_Name, PK_Column and Selected_ID and also Parent_Table, Parent_Column and Selected_Parent_ID, I need to get all columns and their values from tables TableA, TableB and TableC.

How my SQL query should look like? my desired result set should contain columns Selected_ID, TableA_Name, TableA_Desc, TableB_Name, TableB_Desc, TableC_Name, TableC_Desc , Selected_Parent_ID, Parent_Name, Parent_Desc.

Note: TableA, TableB, TableC columns can grow so I need to get all columns and values from these tables in my new results.

I would really appreciate a bit of help. Thx in Advance.

You did a great job posting tables and sample data. But it is not clear at all what you want as output. And by the way, you do not need to do any looping here. — Sean Lange, May 25 '18 at 14:10
may be I dont need loop but my desired result set should contain columns Selected_ID, TableA_Name, TableA_Desc, TableB_Name, TableB_Desc, TableC_Name, TableC_Desc , Selected_Parent_ID, Parent_Name, Parent_Desc. Hierarchy_Table contains only ID columns I need to get text columns.. — sharu, May 25 '18 at 14:19
But what are the actual values you need from your sample data? And please update the question because comments are horrible for formatting. :) — Sean Lange, May 25 '18 at 14:24

SQL Server: Hierarchical data

0 Answers0