September 18, 2007 at 9:21 am
Part of this question came from another thread where I was trying to help someone. Another part came from something I'm trying to do at work.
Here's a scenario. Say I'm tracking sales per month/year based on ProductName (ProductName being the pivot columns), but every occasionally we add a new ProductName to the base Products table. My actual PIVOT query is in a View that I want to automatically adjust and add new columns based on the existing products.
So, if I have a pivot table that has:
YrMo ProdA ProdB ProdC
200512 125.00 35.00 45.00
200601 10.00 25.00 15.00
200602 75.00 180.00 12.00
And the query that's pulling it is:
Select YrMo, ProdA, ProdB, ProdC
from (Select YrMo, ProductName, Sales
from MySales) as SalesPerYrMo
PIVOT
( Sum(Sales) for ProductName
IN (ProdA, ProdB, ProdC) ) as ProductSales
Order by YrMo;
What do I do so that when I add ProdD, and ProdE and later on ProdF, I don't actually have to go back into the View and update the column lists?
FYI: I've already tried doing a subquery underneath the PIVOT operator and got syntax errors. (Incorrect syntax near the keyword 'Select' and Incorrect syntax near ')' ).
Any help, or links to references, would be greatly appreciated. I guess I'd like to know if this can even be done at all...
September 18, 2007 at 9:50 am
Yes it can be done. Don't have time to really explain, but the stored procedure below is dynamic both in X and Y axis. Sample of the result set at the bottom, run with a value of X = 4
CREATE
procedure [dbo].[adm_ReportLastXWeeksOfGrowth] (@x int = 10)
as
/*****************
Anders Pedersen, 12/29/2006
******************/
set
quoted_identifier off
declare
@sql varchar(1000);
set
@sql = 'select *
from (select substring(name,1,(dbo.fcn_CharindexFiles(name))) as DBName, sum([Size (MB)]) [Size (MB)] , convert(varchar(10),timestamp,101) as timestamp
from dbo.GrowthDetails
group by substring(name,1,(dbo.fcn_CharindexFiles(name))), convert(varchar(10),timestamp,101)
) as B
pivot(
sum([Size (MB)])
for timestamp in('
+
stuff
(
(
select N',' + quotename(convert(varchar(10),timestamp,101)) as [text()]
from (select distinct top (@x) timestamp from GrowthDetails order by timestamp desc) as D
order by timestamp
for XML Path('')), 1, 1, N'')+
')) as P;'
;
--select @sql
exec
( @sql)
-- output doesn't look very nice here...
DBName 08/26/2007 09/02/2007 09/09/2007 09/16/2007
-------------------------------------------------- ----------- ----------- ----------- -----------
ACORD 10 10 10 10
Actuary 18421 20008 20008 21754
September 18, 2007 at 12:00 pm
Stuff isn't working for me. It's complaining that my subquery is returning more than 1 value.
I guess I am going to have to do this the hard way. I really really wanted to avoid loops and cursors... Darnit.
September 19, 2007 at 4:55 am
Noeld,
This link was EXACTLY what I needed. Thanks! I am emailing cookies to you. @=)
...or kudos or something... @=)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply