How can i unpivots with dynamic columns or redesign the relational table for me?

I want to use column name to be join another tables.

I have invoice table to store detail of invoice and post some column ' s record to another table .

Invoice table

Invoice_Name  |   Invoice_Amount |  Invoice_Vat | Invoice_Total

Inv001            |          1000           |       70          |     1070             

Account_table

Account_No |  Account_Number | Data_Source

JV001          |     1111               |    Invoice_Amount  ---->1000

JV001          |     1112               |    Invoice_Vat         ---->    70

JV001          |     1113               |    Invoice_Total       ----> 1070 

I want to join  Invoice table to Account_table ON  Invoice_Amount , Invoice_Vat , Invoice_Total with Data_Source

The way i got so far I unpivot  Invoice table  column into row and join with Account_table .


The problem is ,  if the column in Invoice_table are created  ,  I must used dynamic columns to do this in sql query.

I think this is not the right way to do this .

Are there any way to do something like this ?



September 3rd, 2015 3:32pm

Do you mean if the invoice table structure is going to be changed? Because if the invoice table is already defined and you know its columns, then it's static UNPIVOT and then a simple JOIN.
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 3:40pm

Hello Hemkoe,

Your question is not clear to me.

Can you please post more code and less stories (description and values as text)?

Please post

1. Queries to create the relevant tables

2. Queries to insert the sample data

3. In the content of the massage just give us the result that you want to get

This should help us to help you
T

September 3rd, 2015 3:48pm

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

 


Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 6:36pm

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

September 3rd, 2015 6:37pm

Why do we have such strange structure in the Accounts table? What is the relationship between Accounts and Invoices? Can one account have multiple invoices? In this case we would add Account_Id column to Invoices table and join base
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 6:40pm


, 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

OK... great, now we have the basic information and we can discuss the issue :-)

>> if the invoice table structure is  changed

My guess is that you mean that the invoice table data changed and not the structure :-)

If you change the structure then we have no idea on what your case since everything that you explain will not much. For example if you drop the column [subject] then we can not HOIN the tables using this column.

I guess that you mean that you change the data, for example add another row with Inv003 or delete Inv002, etc'

I will give you the answer regarding this assumption, please inform me if my guess was wrong :-)

---- solution ---

the solution is based on DYNAMIC QUERY. We will create a text string which include our query, instead of execute it directly. Next we will use the built-in stored procedure sp_executesql in order to execute out query (using the string we build dynamically... this is why it named DYNAMIC QUERY).

Let's examine your query using dynamic query (at first without any change... just to make sure we wote it currectly (for example if there is ' in the query then once we insert it into string we need to use double '') 

DECLARE @DynamicQuery nvarchar(MAX); -- this will be our dynamic query: a query text that we build dynamicly
-- I insert your original query as the input for this parameter:
-- I did not change anything! I just copy it as it is, since your query does not include any quotation mark
-- dont forget to remove words like "GO" which are not part of the query but only used by the SSMS
SET @DynamicQuery = N'
;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
Select *  from con c 
inner join dbo.GSS_AC_FORMATSUB b on c.subject = b.AccountType_code  COLLATE SQL_Latin1_General_CP1_CI_AS 
';
-- Now we can execute you query using the sp_executesql
EXECUTE sp_executesql @DynamicQuery
GO

OK, this execute great :-)

Now we just need to "fix the query" in order to get the Invoice_Name dynamically. can you change the text dynamically each time according the new data?

Get the new information from the table, and change the text according

* it is not clear to me which Invoice_Name you want to use for the Invoice_Amount, and which one for the Invoice_Vat, and which one for the Invoice_Total, if this is dynamically. Please try to continue your self, or clarifys more regarding this issue

** Please post an example of changing that you do, which you need new query for it.

*** Check out Naomi response! You probably should improve the database structure and avoid these

September 4th, 2015 4:26am

Hi,

Are you trying to dynamically unpivot Invoice table

DECLARE @X XML = (SELECT * FROM Invoice FOR XML PATH(''))
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @X.nodes('//*[text()]') x(i)

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 2:00am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics