Hi all,I need to transform the following table...CREATE TABLE #VendorProductAggregateAmounts ( vpId INT , 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)prototype like....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!-------------Charlie
Table Transformation
Posted - 2007-08-02 : 05:32:41