SELECT     (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
	       (SELECT stdfirstn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strFirstName,
           te.strNote
from       tblM2EvaluationElementReponse as te
INNER JOIN tblM2EvaluationElement as tee
ON         tee.intEvaluationElementId = te.intEvaluationElementId
WHERE      tee.intEvaluationId = 22 
AND        te.strNote <> ''
ORDER BY   tee.intEvaluationElementId,strLastName,strFirstName

Albert	        Natacha	        a	
Bastarache	Anika	        b	
Bernard	        Geneviève	c	
Boudreau	Anabel	        d	
Bourque 	Ashley	        a	
Chiasson	Guillaume	b	
Cormier	        James	        c	
Cormier	        Mireille	d	
Després	        Sylvie	        d	
Albert	        Natacha	        a	
Bastarache	Anika	        a	
Bernard	        Geneviève	a	
Boudreau	Anabel	        a	
Bourque 	Ashley	        a	
Chiasson	Guillaume	a	
Cormier	        James	        a	
Cormier	        Mireille	a	
Després	        Sylvie	        a	

quote:

---

Originally posted by revdnrdy

Hello;

I think you will want to look at the PARTITION clause of sql for this.

There are some good examples on this forum (its how I learned to use it!). Do a search and see what you come up with.

r&r

---

quote:

---

Originally posted by bklr

declare @temp table(name varchar(32),grade varchar(4),ques varchar(4))
insert into @temp select 'Luc', 'A', 'Q1' union all select
'Julie', 'C' ,'Q1' union all select
'Luc', 'D' ,'Q2' union all select
'Julie', 'A', 'Q2'

select * from @temp

SELECT name,[Q1] AS Q1,[Q2] AS Q2
FROM
(SELECT * FROM @temp) AS P
PIVOT ( max(grade) FOR ques IN ([Q1],[Q2]))AS PVT

use this dynamic cross tab
select name,max(case when ques = 'q1' then grade end)as ql,max(case when ques = 'q2' then grade end)as q2
from @temp
group by name

---

quote:

---

Originally posted by bklr

ur welcome
if ur using sql2005, u can use pivot
in sql2000, pivot will not work use dynamic cross tab

---

declare 
@temp table(
	  strName varchar(50),	
	  strGrade int,
	  strQues varchar(50))


insert into @temp  
SELECT     (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
           te.strNote,tee.strTitre 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 14 
AND        te.strNote <> ''
ORDER BY   tee.intEvaluationElementId,strLastName 

select * from @temp
SELECT strName,[Q1] AS Q1,[Q2] AS Q2,[Q3] AS Q3
FROM
(SELECT * FROM @temp) AS P
PIVOT ( max (strGrade) FOR strQues IN ([Q1],[Q2],[Q3]))AS PVT

select * from @temp
SELECT strName,[1] AS Q1,[2] AS Q2,[3] AS Q3
FROM
(SELECT * FROM @temp) AS P
PIVOT ( max (strGrade) FOR strQues IN ([1],[2],[3]))AS PVT

quote:

---

Originally posted by visakh16

something like:-

select * from @temp
SELECT strName,[1] AS Q1,[2] AS Q2,[3] AS Q3
FROM
(SELECT * FROM @temp) AS P
PIVOT ( max (strGrade) FOR strQues IN ([1],[2],[3]))AS PVT

---

quote:

---

Originally posted by visakh16

what are possible values of strQues field?

---

quote:

---

Originally posted by visakh16

oh..so that means values are not known before. then you need this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

---

declare 
@temp table(
	  strName varchar(50),	
	  strGrade varchar(50),
	  strQues varchar(50))




insert into @temp  
SELECT     (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
           te.strNote,tee.intEvaluationElementId 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 14 
AND        te.strNote <> ''
ORDER BY   tee.intEvaluationElementId,strLastName 


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

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

open c
fetch c into @col
while @@FETCH_STATUS = 0
	begin
	select @sum = @sum + ' SUM(['+ cast(@col as varchar(30)) +']) AS ['+ cast(@col as varchar(30)) +'],',
		   @pivot = @pivot + ' ['+ cast(@col as varchar(30)) +'], '
	fetch c into @col
	end
close c
deallocate  c

set @sql = 'SELECT strName, '+ left(@sum, len(@sum)-1)+ 
			' FROM @temp PIVOT (Max(strGrade) 
			FOR strQues IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Ques 
			GROUP BY strName'


exec(@sql)