Help with pivot query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with pivot query

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-03 : 14:34:31

Hi,

I have a query that i use pivot and a temp table that work great!
Just to let you know i am not use to work with temp table and pivot
I use pivot because there can be multiple column and the column are not know before.
Here the result :


Name              FirstName     Q1      Q2      Q3
Albert	          Natacha	A	B	D
Bastarache	  Anika		B	A
Bernard	          Geneviève			C
Boudreau	  Anabel			
Bourque 	  Ashley			
Chiasson	  Guillaume			A			
Sanipass	  Jesse		A	A

Here the code i use to achive this


CREATE TABLE #temp (
  strLastName varchar(50),	
  strFirstName varchar(50),
  strNote varchar(50),
  strQues varchar(50) )



insert into #temp  
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,
		   upper(te.strNote),tee.intEvaluationElementId 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 29 
--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 + ' MAX(['+ 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 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'

Now i need to transform the query to count how many
A in Q1, How many B in Q1, How many C in Q1 and how many D in Q1
A in Q2, How many B in Q2, How many C in Q2 and how many D in Q2
Etc..

So the result i need is
Q1 A = 2
Q1 B = 1
Q1 C =
Q1 D =
Q2 A = 2
Q2 B = 1
Q2 C =
Q2 D =
etc
Or something similar

I hope i explain my self write thanks again for your help!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-03 : 16:43:09

Try this:


CREATE TABLE #temp (

  strNote varchar(50),
  ctr	  int,
  strQues varchar(50) )


SELECT	upper(te.strNote), 
		1 as ctr, 
		tee.intEvaluationElementId 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 29 
--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 + ' MAX(['+ 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


print @sum
print @pivot

set @sql = 'SELECT strNote, '+ left(@sum, len(@sum)-1)+ 
			' FROM #temp PIVOT (SUM(ctr)
			FOR strQues IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Ques 
			GROUP BY strNote'


exec (@sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 03:10:09

what you want is this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-04 : 09:09:30

Hi tks both for the replay i will try it and let you know!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:10:44

ok...you're welcome
let us know how you got on

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-05 : 07:43:09

Hi sorry i was not at work yesterday!

When i try what tonymorell10 give me i get

Invalid length parameter passed to the SUBSTRING function.
for

set @sql = 'SELECT strNote, '+ left(@sum, len(@sum)-1)+
' FROM #temp PIVOT (SUM(ctr)
FOR strQues IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Ques
GROUP BY strNote'

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 07:51:30

Were there values in @sum and @pivot? You would get that if they are empty.

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-05 : 08:11:33

Yes
print @sum
print @pivot

give me

A 1 34
A 1 35
B 1 35
B 1 35
C 1 36
A 1 36
A 1 36
D 1 36
A 1 36

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-05 : 08:18:52

Hey it work my when i copy tonymorell10 there where just missing

insert into #temp before the temp the select
hehe :)

Thanks again! both of you!

I just want to take the time to say thank you because you guys help me a lot in the past!!
A lot of thing i coud't of done with out you!!!

Tks and have a great day!!!
Sorry for my bad english!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 08:23:34

Your very welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:00:47

quote:
Originally posted by lucsky8

Hey it work my when i copy tonymorell10 there where just missing

insert into #temp before the temp the select
hehe :)

Thanks again! both of you!

I just want to take the time to say thank you because you guys help me a lot in the past!!
A lot of thing i coud't of done with out you!!!

Tks and have a great day!!!
Sorry for my bad english!

no problem...you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources