March 19, 2009 at 3:32 pm
Hi Everyone,
Have only recently looked into using the new PIVOT command (well started looking into it a good few times and kept giving up at the first hurdle - the database at work has been upgraded to 2k5 and not been set to comp level 90 !!!). Now I have managed to produce some code which works, I just keep getting a niggling feeling there must be a better or more efficient way to do it...
DECLARE @Fields VARCHAR(2000)
SET @Fields = (SELECT Replace('[' + Name + '], ', '&', '+') AS [text()] FROM CaseType WHERE CaseTypeRef IN(SELECT CaseTypeRef FROM [Case]) ORDER BY Name ASC FOR XML PATH(''))
SET @Fields = LEFT(@Fields, LEN(@Fields)-1 )
EXEC('SELECT ' + @Fields + ' FROM
(SELECT c.CaseNo [CallNo], replace(ct.Name, ''&'', ''+'') [CaseType]
FROM [Case] c
INNER JOIN CaseType ct ON ct.CaseTypeRef = c.CaseTypeRef) SourceTable
PIVOT
(Count(CallNo)
FOR CaseType IN (' + @Fields + ') ) AS PivotTable'
)
Any ideas on making this more efficient/improving the code? BTW: One of the columns of [CaseType].[Name] has an ampersand in it, hence using the replace, I presume this is due to the XML PATH.
Thank you,
Dave
March 23, 2009 at 4:40 am
Dave,
A couple of things. First, don't use text() as a column name. That's a terrible habit to get into. In fact, if you can avoid it, don't use keywords as table or column names or bizarre characters at all.
I usually use the COALESCE function in setting up my fields and then assign my dynamic SQL to a variable. This way, for troubleshooting purposes, I can SELECT the variable just to make sure everything looks proper.
Here's a shortened example of one of my dynamic pivot statements.
Declare @cols varchar(4000);
Select @cols = COALESCE(@cols + '], [' + Acct_Date, Acct_Date ) from TempAcctDates;
Set @cols = '[' + @cols + ']';
--Select @Cols; --For troubleshooting purposes
Declare @PivotSQL varchar(8000);
set @PivotSQL = 'Select Prod_Line, Code, [Desc], Description, ' + @cols +
+ ' FROM (Select pl.Description, s.[Desc], rs.Acct_Date, rs.Prod_Line, s.Code, '
+ 'CASE When isnull(rs.Nbr_Paid,0) = 0 Then 0.00 Else rs.[Sum of Paid] / rs.Nbr_Paid END as AvgPymt '
+ 'from Rolling12_Summary rs INNER JOIN Prod_Line pl ON rs.PROD_LINE = pl.Product '
+ 'INNER JOIN Source s ON rs.SOURCE = s.CODE) as MyAvgPymt '
+ 'PIVOT ( SUM(AvgPymt) for Acct_Date IN ( ' + @cols + ' ) ) as AveragePymts';
--Select @PivotSQL; --For troubleshooting purposes
Exec (@PivotSQL);
WOW. Really not liking how the new CODE surrounds take out lines between code paragraphs. That's awful.
March 23, 2009 at 4:47 am
Might I also recommend using a JOIN on your @Fields query instead of a sub-query WHERE clause?
IE:
FROM CaseType ct
JOIN [CASE] c
on ct.CaseTypeRef = c.CaseTypeRef
ORDER BY Name ASC FOR XML PATH('')
Works a bit more efficiently than the subquery stuff.
March 23, 2009 at 4:50 am
Here is a pretty good article by Jeff, about dynamic pivoting using cross-tabs which seem to be faster than actually using the PIVOT function in 2005.
check it out:
http://www.sqlservercentral.com/articles/cross+tab/65048/
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 23, 2009 at 11:08 am
Thanks for your help Brandie, I have adapted the coalesce code, which works a treat!!
Now I have one small problem, my intention was to use a CTE for the base data, then have a Unique CTE based on that...
DECLARE @Cols VARCHAR(4000);
With Data AS(
SELECTc.CaseNo [CallNo],
ct.Name [Outcome]
FROM [Case] c
INNER JOIN dbo.CaseType ct ON ct.CaseTypeRef = c.CaseTypeRef)
, UniqueList AS (SELECT DISTINCT Outcome FROM Data)
SELECT @Cols = COALESCE(@Cols + '], [' + Outcome, Outcome ) FROM UniqueList ORDER BY Outcome;
SET @Cols = '[CallNo], [' + @Cols + ']';
SELECT @Cols;
EXEC('SELECT ' + @Cols + 'FROM Data PIVOT ( Count(CallNo) FOR Outcome IN (' + @Cols + ') ) AS PivotTable')
This obviously produces an error as it cannot find the [Data] object. Is there a way around this without duplicating code... and wait for it... on a database which I do not have write access to - therefore no way of creating views.
Thanks,
Dave
March 23, 2009 at 11:22 am
Select your CTE into a Temp Table (or create the TempTable first for better performance and then do the CTE...Insert). Then work off the Temp table.
That should resolve the issue for you.
EDIT: I just caught the "no write access" part. Make sure you have CREATE Table permissions. Otherwise, the Temp Table solution won't work. CTE's have to be used in the first statement following the CTE in order to work efficiently. And if you can't create a temp table, try using a table variable (though that gets messy).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply