I need to transform the following table...
CREATE TABLE #VendorProductAggregateAmounts (
, employeeId INT
, employeeCost MONEY DEFAULT NULL
, employerCost MONEY DEFAULT NULL
, taxFreeAmount MONEY DEFAULT NULL
with data in the form....
INSERT INTO #VendorProductAggregateAmounts
SELECT 1, 201, 0.0000, 0.0000, 0.0000 UNION ALL
SELECT 7, 201, 712.8900, 475.2500, 0.0000 UNION ALL
SELECT 9, 201, 0.0000, 0.0000, 0.0000 UNION ALL
SELECT 12, 201, 84.6600, 84.6600, 0.0000
into a #results table with 1 line per employeeId with 3 columns for each vpid (column names to have the form (description + (employee / employer / taxFreeAmount) (description taken from the vendorProduct table (referenced by vpId)
employeeId || vpId.descrip (employee) || vpId.description (employer) || vpid.description (taxFreeAllowance) || .........
Is there any better way to do this than setting up a cursor (or loop) to alter a #results table adding the descriptions+(employee/employer etc) columns using dynamic sql and then perform a second loop to insert the data into the as yet unknown column names using more dynamic sql.
I know that the I really shouldn't be doing this in SQL it's a task for a client application but I've got to work with what I've got to work with.
Any help would be appreciated!