[Q] complex output result

[reneecccwest]

Hello, I have three tables and would like to see the output result as
below:

--------------------- OUTPUT RESULT
--------------------------------------
index_id officeid officename officecode officedescript
Description attrcatitemid departmentID divisionID branchID
sectionID unitID

6 9 00012 02 NNN Department 133 6 6 6 6 6
3 9 00012 05 CCC Department 133 3 3 3 3 3
5 9 00012 03 AAB Division 134 3 5 5 5 5
9 9 00012 10 jjj Branch 135 3 5 9 9 9
4 9 00012 04 VVV Division 134 3 4 4 4 4
8 9 00012 08 lll Branch 135 3 4 8 8 8
10 9 00012 11 bbn Section 136 3 4 8 10 10
11 9 00012 12 vcc Unit 137 3 4 8 10 11
12 9 00012 13 uuu Section 136 3 4 8 12 12
1 9 00012 09 AAA Department 133 1 1 1 1 1
2 2 00013 01 BBB Department 133 2 2 2 2 2
7 2 00013 07 PPP Division 134 2 7 7 7 7

--------------------- OUTPUT RESULT END
-----------------------------------

table relationship:
A_Office.index_id = organization.officeid
A_Type.index_id = organization.attrcatitemid
organization.index_id = organization.departmentID
organization.index_id = organization.divisionID
organization.index_id = organization.branchID
organization.index_id = organization.sectionID
organization.index_id = organization.unitID

-------------------------------------------------------
Generating tables

A_Office table Script:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[A_Office]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[A_Office]
GO

CREATE TABLE [dbo].[A_Office] (
[index_id] [int] NOT NULL ,
[officename] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

INSERT INTO A_Office (
[index_id], [officename])
VALUES (1, '00011')

GO
INSERT INTO A_Office (
[index_id], [officename])
VALUES (2, '00012')

GO
INSERT INTO A_Office (
[index_id], [officename])
VALUES (3, '00014')
GO
INSERT INTO A_Office (
[index_id], [officename])
VALUES (9, '00013')

----------------------------------

A_Type Table:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[A_Type]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[A_Type]
GO

CREATE TABLE [dbo].[A_Type] (
[index_id] [int] NOT NULL ,
[description] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[sortorder] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO A_Type (
[index_id], [description],[sortorder])
VALUES (133, 'Department', 1)
GO
INSERT INTO A_Type (
[index_id], [description],[sortorder])
VALUES (134, 'Division', 2)
GO
INSERT INTO A_Type (
[index_id], [description],[sortorder])
VALUES (135, 'Branch', 3)
GO
------------------------------------------
organization table script:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[organization]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[organization]
GO

CREATE TABLE [dbo].[organization] (
[index_id] [int] NOT NULL ,
[officeid] [int] NOT NULL ,
[officecode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[officedescript] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[attrcatitemid] [int] NOT NULL ,
[departmentID] [int] NOT NULL ,
[divisionID] [int] NOT NULL ,
[branchID] [int] NOT NULL ,
[sectionID] [int] NOT NULL ,
[unitID] [int] NOT NULL ,
) ON [PRIMARY]
GO

INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (1, 9, '09', 'AAA', 133, 1, 1, 1, 1, 1)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (2, 2, '01', 'BBB', 133, 2, 2, 2, 2, 2)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (3, 9, '05', 'CCC', 133, 3, 3, 3, 3, 3)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (4, 9, '04', 'VVV', 134, 3, 4, 4, 4, 4)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (5, 9, '03', 'AAB', 134, 3, 5, 5, 5, 5)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (6, 9, '02', 'NNN', 133, 6, 6, 6, 6, 6)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (7, 2, '07', 'PPP', 134, 2, 7, 7, 7, 7)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (8, 9, '08', 'LLL', 135, 3, 4, 8, 8, 8)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (9, 9, '10', 'jjj', 135, 3, 5, 9, 9, 9)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (10, 9, '11', 'bbn', 136, 3, 4, 8, 10, 10)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (11, 9, '12', 'vcc', 137, 3, 4, 8, 10, 11)

GO
INSERT INTO organization (
[index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],
[sectionID],[unitID])
VALUES (12, 9, '13', 'uuu', 136, 3