Yes , that is exactly what i mean.
This is my table invoice_table with example data
----------------------------------
USE [TEST_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoice](
[Invoice_Name] [nvarchar](50) NULL,
[Invoice_Amount] [float] NULL,
[Invoice_Vat] [float] NULL,
[Invoice_Total] [float] NULL
) ON [PRIMARY]
GO
------EXAMPLE DATA----
USE [TESTDB]
GO
INSERT [dbo].[Invoice] ([Invoice_Name], [Invoice_Amount], [Invoice_Vat], [Invoice_Total]) VALUES (N'Inv001', 1000, 70, 1070)
GO
INSERT [dbo].[Invoice] ([Invoice_Name], [Invoice_Amount], [Invoice_Vat], [Invoice_Total]) VALUES (N'Inv002', 2000, 140, 2140)
GO
-------------------------------
And this is Account table
-------------------------------------
USE [TEST_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GSS_AC_FORMATSUB](
[AccountSub_id] [int] IDENTITY(1,1) NOT NULL,
[Account_ID] [int] NULL,
[Account_Number] [int] NULL,
[AccountSub_type] [nvarchar](2) NULL,
[AccountType_code] [nvarchar](50) NULL -- this is column to join
) ON [PRIMARY]
GO
------EXAMPLE DATA----
USE [ACCOUNTDB2]
GO
SET IDENTITY_INSERT [dbo].[GSS_AC_FORMATSUB] ON
GO
INSERT [dbo].[GSS_AC_FORMATSUB] ([AccountSub_id], [Account_ID], [Account_Number], [AccountSub_type], [AccountType_code]) VALUES (56, 9, 68, N'Dr', N'pur_1')
GO
INSERT [dbo].[GSS_AC_FORMATSUB] ([AccountSub_id], [Account_ID], [Account_Number], [AccountSub_type], [AccountType_code]) VALUES (57, 9, 24, N'Dr', N'pur_2')
GO
INSERT [dbo].[GSS_AC_FORMATSUB] ([AccountSub_id], [Account_ID], [Account_Number], [AccountSub_type], [AccountType_code]) VALUES (58, 9, 1, N'Cr', N'pur_6')
GO
SET IDENTITY_INSERT [dbo].[GSS_AC_FORMATSUB] OFF
GO
---------------------------------
What i am trying to do is , to created automatic post Invoice detail (Invoice_Amount , Invoice_Vat ,
Invoice_Total) to account system , by matching them to Account_Number (1 by 1). (Invoice_Amount
, Invoice_Vat , Invoice_Total) are in form of columns but Account_Number are in form of rows record.
So what i do so far is , i unpivot invoice table into row by this query
--------------------------
With PUR_INV AS (
SELECT Invoice_Name, Invoice_Amount as pur_1 , Invoice_Vat as pur_2 , Invoice_Total as pur_6
FROM Invoice
) , con As (
select u.Invoice_Name , u.subject , u.marks
from PUR_INV s
unpivot
(
marks
for subject in (pur_1 , pur_2, pur_6 )
) u ) Select * from con
--------------------------------------
and then i join this quey to Account_table
--------------------------------------
With PUR_INV AS (
SELECT Invoice_Name, Invoice_Amount as pur_1 , Invoice_Vat as pur_2 , Invoice_Total as pur_6
FROM Invoice
) , con As (
select u.Invoice_Name , u.subject , u.marks
from PUR_INV s
unpivot
(
marks
for subject in (pur_1 , pur_2, pur_6 )
) u ) Select * from con c inner join ACCOUNTDB2.dbo.GSS_AC_FORMATSUB b on c.subject = b.AccountType_code COLLATE SQL_Latin1_General_CP1_CI_AS
-------------------------
, by this i will got the relation between these two tables.
the problem is if the invoice table structure is changed then i have to rewrite the query each time.
How can i solve my problem ?
but i am not sure are there any better way to do this .
I am OK to redesign the table structure if i have to , I need your suggestion and example.
thanks