PIVOT with optional number of columns.

I had this scenario from one of my developer. We needed to Pivot the columns according to the filter passed by the user on front end. If he passes filter that gives 3 rows, then only three columns to be in PIVOT table. if 2 then t columns and so on.

This is the code i wrote to accomplish the task;



If Not Exists(Select * from sys.objects where [name] = 'tblTable3')
begin
Create Table tblTable3 (ID int, ID2 int,txt varchar(max))

Insert into tblTable3
Select 1,10,'1 - 10' Union All
Select 1,20,'1 - 20' Union All
Select 1,30,'1 - 30' Union All
Select 1,40,'1 - 40' Union All
Select 2,20,'2 - 20' Union All
Select 2,40,'2 - 40' Union All
Select 3,50,'3 - 50'
end

Declare @vCols varchar(Max)
Declare @vPCols varchar(Max)
Declare @vQuery varchar(max)

Declare @vFilterID int
Declare @vFilter varchar(max)

Set @vFilterID = 1 --We need to discuss how this filter will be applied...
Set @vFilter = ' where ID = ' + Cast(@vFilterID as varchar(10))

Set @vPCols = ''
Set @vCols = ''

Select @vCols = @vCols + ',IsNull([' + Cast(ID2 as varchar(10)) + '],'''') as [' + Cast(ID2 as varchar(10)) + ']'
,@vPCols = @vPCols + ',[' + Cast(ID2 as varchar(10)) + ']'
from tblTable3
where ID = @vFilterID

--Select * from tblTable3

Set @vCols = Stuff(@vCols,1,1,'')
Set @vPCols = Stuff(@vPCols,1,1,'')

Set @vQuery = 'Select ID,' + @vCols + '
From (Select * from tblTable3 ' + @vFilter + ') Data
PIVOT (Max(Txt) For ID2 in ('+ @vPCols+')) p '

Print @vQuery
Exec (@vQuery)


Drop Table tblTable3