CREATE TABLE #temp (
		  strLastName varchar(200),	
		  strFirstName varchar(200),
		  strNote varchar(200),
		  strQues varchar(200),
		  intPosition INT )



		insert into #temp  
		SELECT  
				te.stdlastn as strLastName,
				te.stdfirstn as strFirstName,
				tr.T2,
                                td.ID, --that i will use for the groupe by
				rank() over (order by tob.id) as rank --Order i want the pivot table to in

		from	Bulletin.dbo.tblEleves as te
				INNER JOIN tblBulletin as tb ON te.stdnumber=tb.NoEtud
				INNER JOIN tblNomBull as tnb ON tb.NoBull=tnb.ID
				INNER JOIN tblSection as ts ON ts.ID_Bull = tnb.ID
				INNER JOIN tblObjet	as tob ON tob.ID_Sec = ts.ID	
				INNER JOIN tblDescripteur as td ON td.ID_Obj = tob.ID
				INNER JOIN tblReponse as tr ON tr.ID = tb.ID 
		where	intActif = 1 and 
				tb.NoBull not like '%EdPhy%' 
				and tnb.Nom not like '%EdPhy%' 
				and tnb.Date = '2011-2012'
				and tr.[Desc] = td.ID
				and te.stdGradeId = 4
				and ts.Nom = 'français'
				and te.schoolid = 1202
				and td.Nom <> 'L’élève'
		ORDER BY te.schoolid,te.stdlastn,te.stdfirstn,ts.Affichage,tob.Affichage,td.Affichage



		--select distinct strQues,intRank  from #temp order by    intPosition


		declare c cursor  for select distinct strQues  from #temp order by strQues		declare @pivot varchar(1000), @sum varchar(1000), @sql nvarchar(1000), @col int

		select @pivot='',  @sum=''

		open c
		fetch c into @col
		while @@FETCH_STATUS = 0
			begin
			select @sum = @sum + ' MAX(['+ cast(@col as varchar(200)) +']) AS ['+ cast(@col as varchar(200)) +'],',
				   @pivot = @pivot + ' ['+ cast(@col as varchar(200)) +'], '
			fetch c into @col
			end
		close c
		deallocate  c
		
		set @sql = 'SELECT strLastName,strFirstName,'+ left(@sum, len(@sum)-1)+ 
					' FROM #temp PIVOT (Max(strNote) 
					FOR strQues IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Ques 
					GROUP BY strFirstName,strLastName
					ORDER BY strLastName,strFirstName'


		exec(@sql)
		drop table #temp

quote:

---

Originally posted by visakh16

why do you need cursor for dynamic pivoting? wont this suffice?
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE TABLE #temp (
  strLastName varchar(200),	
  strFirstName varchar(200),
  strNote varchar(200),
  strQues varchar(200), 
  intRank int)



insert into #temp  
SELECT  te.stdlastn as strLastName,
		te.stdfirstn as strFirstName,
		tr.T2,td.ID,
		rank() over (order by tr.num) as rank
from	Bulletin.dbo.tblEleves as te
		INNER JOIN Bulletin.dbo.tblBulletin as tb ON te.stdnumber=tb.NoEtud
		INNER JOIN Bulletin.dbo.tblNomBull as tnb ON tb.NoBull=tnb.ID
		INNER JOIN Bulletin.dbo.tblSection as ts ON ts.ID_Bull = tnb.ID
		INNER JOIN Bulletin.dbo.tblObjet	as tob ON tob.ID_Sec = ts.ID	
		INNER JOIN Bulletin.dbo.tblDescripteur as td ON td.ID_Obj = tob.ID
		INNER JOIN Bulletin.dbo.tblReponse as tr ON tr.ID = tb.ID 
where	intActif = 1 and 
		tb.NoBull not like '%EdPhy%' 
		and tnb.Nom not like '%EdPhy%' 
		and tnb.Date = '2011-2012'
		--and tb.Niveau = 'DEUXIÈME année'
		--and te.schoolid = @intEcoleId
		and tr.[Desc] = td.ID
		and te.stdGradeId = 4
		and ts.Nom = 'français'
		and te.schoolid = 1202
ORDER BY        te.schoolid,te.stdlastn,te.stdfirstn,ts.Affichage,tob.Affichage,td.Affichage



DECLARE @query VARCHAR(4000)
DECLARE @question VARCHAR(2000)
SELECT  @question = STUFF(( SELECT DISTINCT 
			'],[' + ltrim(str(strQues))
                        FROM    #temp
                        ORDER BY '],[' + ltrim(str((strQues))) 
                        FOR XML PATH('')
			), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
	SELECT strLastName,strFirstName,strQues,strNote
	FROM #temp
)t
PIVOT (MIN(strNote) FOR strQues
IN ('+@question+')) AS pvt'

EXECUTE (@query)


drop table #temp

quote:

---

Originally posted by visakh16

why are concerned on order of sequence of columns? you can always return them in order you want at frond end application

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

while pivoting are you generating row list based on order of rank?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT  @question = STUFF(( SELECT DISTINCT 
			'],[' + ltrim(str(strQues))
                        FROM    #temp
                        ORDER BY '],[' + ltrim(str((intRank))) 
                        FOR XML PATH('')
			), 1, 2, '') + ']'

quote:

---

SELECT @question = STUFF(( SELECT DISTINCT
'],[' + strQues
FROM #temp
ORDER BY '],[' + intRank
FOR XML PATH('')
), 1, 2, '') + ']'

---

quote:

---

Originally posted by visakh16

yup, but make it numeric based dont do the convert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

SELECT @question = STUFF(( SELECT
'],[' + ltrim(str(strQues))
FROM #temp
GROUP BY '],[' + ltrim(str(strQues))
ORDER BY '' + convert(int,min(intRank))
FOR XML PATH('')
), 1, 2, '') + ']'

---