I have two tables, one is a holding tank for data, and another is to store user defined column headers for the columns in the holding tank.
CREATE TABLE [dbo].[HoldingTank]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](max) NULL,
[B] [nvarchar](max) NULL,
[C] [nvarchar](max) NULL,
[D] [nvarchar](max) NULL
CONSTRAINT [PK_HoldingTank]
PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[HoldingTank] ON
INSERT [dbo].[HoldingTank] ([Id], [A], [B], [C], [D])
VALUES (1, N'VALUE1', N'VALUE2', N'VALUE3', N'VALUE4')
SET IDENTITY_INSERT [dbo].[HoldingTank] OFF
CREATE TABLE [dbo].[ColumnHeaders]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ColumnLetter] [nvarchar](2) NOT NULL,
[ColumnHeading] [nvarchar](50) NOT NULL,
[IsUsed] [bit] NOT NULL,
CONSTRAINT [PK_ColumnHeaders]
PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ColumnHeaders] ON
INSERT [dbo].[ColumnHeaders] ([Id], [ColumnLetter], [ColumnHeading], [IsUsed])
VALUES (1, N'A', N'Custom1', 1),
(2, N'B', N'Custom2', 1),
(3, N'C', N'Custom3', 1),
(4, N'D', N'Custom4', 1)
GO
SET IDENTITY_INSERT [dbo].[ColumnHeaders] OFF
The goal is build a query that return the ColumnHeading values from the ColumnHeaders table in the place of the normal column headings in the HoldingTank, so instead of
Id A B C D
------ ------ ------ ------ ------
1 VALUE1 VALUE2 VALUE3 VALUE4
it would return:
Id Custom1 Custom2 Custom3 Custom4
------ --------------- --------------- --------------- ---------------
1 VALUE1 VALUE2 VALUE3 VALUE4
Pretty sure this is either a PIVOT or a combination of PIVOT and UNPIVOT.
Unfortunately, every variation and example I have tried always seems to assume a static (and known ahead of time) value in the headers table. Unless I'm missing something incredibly basic.
Any help would be appreciated!
