Dynamic Pivots - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dynamic Pivots

Author

Topic

DavisJ44
Starting Member

9 Posts

Posted - 2009-03-05 : 13:54:12

I know how to write a pivot in SQL Server, but I'm trying to replace the pivot headings with a variable and not having much luck.

You can see below that I'm setting a variable then I'm trying to use that variable has headings in my pivot, which it doesn't like. If anyone knows how to accomplish a "DYNAMIC PIVOT" I would appreciate some insight.

declare @CurYr int;
select @CurYr = max(acctg_qtr)
from txmdsd.st_gl_loss
select @CurYr

select YearsLater, @CurYr-8,@CurYr-7,@CurYr-6,@CurYr-5,@CurYr-4,@CurYr-3,@CurYr-2,@CurYr-1,@CurYr
from ( Table) a
pivot( sum(PaidLoss)
FOR AccYr in ( [@CurYr-8],[@CurYr-7],[@CurYr-6],[@CurYr-5], [@CurYr-4],[@CurYr-3],[@CurYr-2],[@CurYr-1],[@CurYr] )) pvt

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2009-03-05 : 15:53:38

Try building a SQL string:

DECLARE @DynamicSQL VARCHAR(8000)

and running:

EXEC(@DynamicSQL);

DavisJ44
Starting Member

9 Posts

Posted - 2009-03-05 : 16:41:17

It worked Thanks

Bodestone
Starting Member

18 Posts

Posted - 2009-03-05 : 17:53:54

I tend to steer clear of dynamic SQL if I can. Apart from the possible security implecations if the execution is exposed to the outside world the dynamic queries don't benefit from stored optimisation etc.

If it is only the pivot headers you want to be dynamic and you can apply column headers at the interface layer then this peice of test code may give you some ideas. Oh, assuming that the actuall number of columns is fixed of course.

I have included the whole test shebang. I actually find it really useful to store create and store example queries like this for re-use later:


/**
*  Semi Flexible Pivot Example
**/

DECLARE	@currentYear int
DECLARE	@yearsBack int
DECLARE	@myData TABLE (myID int, myDate datetime, myVal int)
DECLARE @counter int
SET	@currentYear = YEAR(GETDATE())

SET	@counter = 10
WHILE	@counter > -1
BEGIN
	INSERT	@myData
	SELECT	1, '01 Jan ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 10000
	UNION
	SELECT	2, '01 Jan ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 20000
	UNION
	SELECT	1, '01 Feb ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 10000
	UNION
	SELECT	2, '01 Feb ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 20000
	SET	@counter = @counter - 1
END

SELECT	myID, [10],[9],,[7],[6],[5],[4],[3],[2],[1]
FROM	(
			SELECT	myID,
					myVal,
					NTILE(10) OVER(PARTITION BY MyID ORDER BY YEAR(myDate) DESC) AS yearOffset
			FROM	@myData
		) ma
		PIVOT
		(
			sum(MyVal) FOR [yearOffset] IN ([10],[9],,[7],[6],[5],[4],[3],[2],[1])
		) p

Bodestone
Starting Member

18 Posts

Posted - 2009-03-05 : 18:18:12

Sorry for all the edits. Got playing and realised I had some inconsistencies with summing dates, thought I got it then typod then realised a nother method. Each time I thought it was final post for this example but playing found me better ways

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources