Looking at the original query it is very de-normalized and almost looks like a pivot table in its own right so the first thing i did to accomplish the task was to UNPIVOT the data (i used a cross apply) First i generated some sample data
CREATE TABLE SampleData (
EffectiveDate DATE,
CanDrinks INT, --ints are easy to generate
Bottle20CSD INT,
Snacks INT,
Coffee INT,
Food INT
--..... this gives enough columns to get the point accross
)
INSERT INTO SampleData
SELECT DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 25) * -1,GETDATE()),
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000
FROM Tally
WHERE N < 20
;WITH Dupes AS (SELECT ROW_NUMBER() OVER (PARTITION BY EffectiveDate ORDER BY CanDrinks) AS RN, *
FROM SampleData)
DELETE FROM Dupes WHERE RN > 1
SELECT * FROM SampleData
Then with data that is close to what i think yours is i normalized the data on the fly with
SELECT EffectiveDate, ItemName, Ammount
FROM SampleData
CROSS APPLY (VALUES ('CanDrinks',CanDrinks),('Bottle20CSD',Bottle20CSD),('Snacks',Snacks),('Coffee',Coffee),('Food',Food))x(ItemName,Ammount)
This will allow us to make a nice pivot table in the output you want. With the unpivot you can then write your dynamic pivot table (or cross tab) in the same manor you were attacking the problem before. if you have any other questions let me know.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]