I am working with an accounting package backend.
In SQL Server 2012 there is a custom fields table with Values (see sample below) and another table with custom header names (see sample below).
I have created a query that joins the 2 tables multiple times and transposes data into columns and uses aliases to name the column. It was a tedious task with over 50 custom fields.
There have been some field changes and Executives are talking about adding even more fields.
I am looking for a better solution to dynamically accommodate field name changes or adds. Any suggestions will be appreciated.
Values Table:
Company,JobNumber,FieldNumber,Information
'01',12345,'01','Value1'
'01',12345,'02','Value2'
...
'01',12345,'50','Value50'
'02',12345,'01','Value2_1'
Header Name Table:
Company,FieldNumber,Description
'01','01','ColumnName1'
'01','02','ColumnName2'
'01','03','ColumnName3'
...
'01','50','ColumnName50'
'02','01','ColumnName2_1'
The query below will dynamically Name ColumnName1 and show values in Column for Company 1.
Declare @CompanyCode nvarchar(2)
SET @CompanyCode = '01'
Declare @CustomInfo1 varchar(20)
Set @CustomerInfo1 = (Select TOP 1 H.Description
from dbo.HeaderTable AS H
where H.CompanyCode = @CompanyCode and H.FieldNumber = '01')
DECLARE @sql nvarchar(2000)
Set @sql = 'SELECT V.JobNumber, V.Information AS [' + @CustomInfo1 + ']
FROM dbo.ValuesTable AS V
Where V.CompanyCode = ' + '''' + @CompanyCode + '''' + 'AND V.Fieldnumber = ''01'''
EXEC sp_executesql @sql
Results:
Company,JobNumber,ColumnName1
'01',12345,'Value1'
Desired Results:
Company,JobNumber,ColumnName1,ColumnName2,,...,ColumnName50
'01',12345,'Value1','Value2',...,'Value50'
'01',12346,'Value1a','Value2a',...,'Value50a'
'02',12345,'Value2_1','Value2_2',...,'Value2_50'
'03',12346,'Value3_1','Value3_2',...,'Value3_50'
The part I am struggling with is adding the next 49 columns. The ColumnNames could be potentially different for the other companies. I could copy this code 50 times with 50 @CustomerInfo 1-50 and Change FieldNumber from 01,02,03...50, then repeat again for company 2-4 with a Select query1, query2, query3, etc. I cannot use a Union to connect each Company since Column Names may be different.
Maybe some kind of loop?
Select Loop(Query Above for Company1), From Table
Union
Select Loop(Query Company2), From Table
Union
Select Loop(Query Company3), From Table
Ultimately I would like use a Stored Procedure or SSIS to create a table, then update, insert, delete to maintain data.
I consider myself between beginner and intermediate in SQL so please go easy on me.
After @Duffy Answer my final query is
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'
SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()]
FROM dbo.Header H
WHERE H.Company = @Company
ORDER BY H.FieldNumber
FOR XML PATH('')),1,1,'')
SET @SQL = '
SELECT Company,JobNumber,'+@Columns+'
FROM
(
SELECT h.Company,RV.jobNumber,RV.information,h.description
FROM dbo.Header h
INNER JOIN dbo.RowValues RV
ON RV.FieldNumber = h.FieldNumber
AND RV.Company = h.Company
WHERE H.Company = ' + '''' + @Company + '''' + '
) as Data
PIVOT
(
MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY JobNumber ASC'
EXEC sp_executesql @sql;
The only change I needed to make was adding the single quotes in the Dynamic SQL statement. Otherwise, SQL was treating the Variable '01' as an int type even though nvarchar was Declared. Thanks again @Duffy