Stored Proc or Table Valued Function? Pivot problem

Question

Post reply

Stored Proc or Table Valued Function? Pivot problem

niteshrajgopal

SSCommitted

Points: 1877
More actions
June 29, 2012 at 12:32 am

#276990

Hi Guys,
I've wriiten a stored procedure to dynamically sum monthly totals in a financial year. However, I need to call it from within a view(which according to research is not possible). I can't even used a table valued function as I'm using a sql string because of the dynamic pivot columns. Here is my stored proc. I need to put this in a view because my front end uses tables or view to build a grid.
I get the start and end date of a financial year. I then call a function "getMonthList" that gives me the month's between the start and end dates. That gives me the columns I then use in the pivot query.
ALTER PROCEDURE [dbo].[DynamicTimeSheetTotals]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @PivotColumnHeaders VARCHAR(MAX)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
Select @StartDate = StartDate from dbo.FinancialYear()
Select @EndDate = EndDate from dbo.FinancialYear()
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + convert(varchar(7),[MONTH],111) + ']',
'[' + convert(varchar(7),[MONTH],111) + ']'
)
FROM dbo.GetMonthList(@StartDate, @EndDate)
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
ts .TaskID, ts .ResourceID, ts .num_hours, convert(varchar(7),ts.work_date,111) AS MM, t .TopicID
FROM OSC_Meeting_TimeSheet ts LEFT OUTER JOIN
OSC_Meeting_Tasks t ON t .TaskID = ts .TaskID
WHERE YEAR(ts .work_Date) >= YEAR(GETDATE())
) TableDate
PIVOT (
SUM(num_hours)
FOR [MM] IN (
' + @PivotColumnHeaders + '
)
) PivotTable
'
EXECUTE(@PivotTableSQL)
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 1

There ia a problem with what you're trying to accomplish.

Basically, you can't do it. Sql doesn't allow dynamic naming of columns in views.

You have to do something like Period1, Period2, Period3, Period4,...,PeriodN , where N is a large enough number to handle the max number of periods you need to support. Then you also have to write a TVF with hardcoded logic to pivot your data (dynamic pivot goes away)

it's going to be fairly ugly, but doable.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

This shouldn't be too difficult.

What does @PivotColumnHeaders look like?

Can you provide DDL for the two tables, with some sample data?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden