Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Table Transformation

Author  Topic 

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2007-08-02 : 05:32:41
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
   

- Advertisement -