Dynamic SQL on Cross-Tab w/ Varying Col Count

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL on Cross-Tab w/ Varying Col Count

Author

Topic

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-25 : 13:18:03

Hi, I have this idea of using Dynamic SQL on a cross-tab and the following questions popped-up:

1) Is there a limit to the length of the statement the EXEC / sp_executesql command can execute?
2) Should I use cursors (Haven't used this one, I preferred work tables)?

Here are the details:

TABLE_SAMPLE
RptDate DATETIME,
SomeField CHAR(1),
ID CHAR(8),
RetVal VARCHAR(15)

SP Input:
A date range that defines what the RptDates are:
@DateFrom DATETIME,
@DateTo DATETIME

SP Output could be:

| SomeField | RptDate1 | RptDate2 | RptDate3 | RptDate4 | RptDate5 | RptDate..n
--------------------------------------------------------------------------------------------------
ID1 | A | RetVal1 | RetVal2 | RetVal3 | [BLANK] | RetVal5 | RetVal..n
ID2 | A | [BLANK] | ... | ... | ... | ... | ...
ID3 | C | [BLANK] | ... | [BLANK] | ... | ... | [BLANK]
ID..n | B | ... | ... | ... | ... | ... | RetVal..n

[BLANK] - means that there was no record for that RptDate for the ID

Conditions:
- The number of records per ID varies and depends on the number of RptDates covered by @DateFrom and @DateTo
ID1 can have records for 02/26/2008, 02/25/2008, 02/22/2008
ID2 can have records for one, two or all of the same dates.
etc...

Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-26 : 02:18:30

I have just completed something similar.

I built a dynamic table depending on the months between the two dates.


set @SQLCmd = 'create table dbo.DynamTab
(
	[a] [int] IDENTITY(1,1) NOT NULL,
	[b] [nvarchar](255) NULL,
	[c] [nvarchar](50) '

	while @i <= @FieldCount
		begin
			set @SQLCmd = @SQLCmd + '[' + convert(varchar,datepart(mm,dateadd(mm,@i,@FirstMonth))) + '/' + convert(varchar,datepart(yy,dateadd(mm,@i,@FirstMonth))) + '] decimal(19,2) NULL CONSTRAINT [DF_DynamTab_' + convert(varchar,datepart(mm,dateadd(mm,@i,@FirstMonth))) + '/' + convert(varchar,datepart(yy,dateadd(mm,@i,@FirstMonth))) + ']  DEFAULT ((0))' + case when @i = @FieldCount then ')' else ',' end
			set @i=@i+1
		end

	exec (@SQLCmd)

Then I populated the table with the values I required for a,b and c.

Then update the dynamic fields by using something like:


while @i <= @FieldCount
	begin
		set @CurrentMonth = (select case when @i = 0 then datepart(mm,@FirstMonth) else datepart(mm,dateadd(mm,@i,@FirstMonth)) end)
		set @CurrentYear = (select case when @i = 0 then datepart(yy,@FirstMonth) else datepart(yy,dateadd(mm,@i,@FirstMonth)) end)

		set @SQLCmd = 'Update dbo.DynamTab
			set [' + convert(varchar,@CurrentMonth) + '/' + convert(varchar,@CurrentYear) + '] = ' <yourvaluehere>
	end

exec @SQLCmd

Obviously I have simpliffied this and you may have to add parts on depending on how you calculate the retvals.