Column Name in separate table SQL Server

Question

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

Answer 1

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'
SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()] 
 FROM dbo.Headers 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 Headers 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;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.

• Matt Cullinan: After a few modifications it worked perfect. Thank you. The issue I experienced was related to the Company. Someone created some dummy companies with text in the names. So I was receiving an error "Unable to convert to int 'dc'" in the Where statement instead of h.company I used Case when isnumeric(h.company) = 1 then h.company end
• Matt Cullinan: After further review I put my final query in my original question. The better way for me to handle the "unable to convert to int" error was to add single quotes in the Dynamic SQL Where clause. Like so WHERE H.Company = ' + '''' + @Company + '''' + '
• Duffy: Interesting, both versions work for me must be some config setting governing what it thinks is an implicit conversion perhaps? Well at least it works either way!