pivot for 3 sql statemnets - 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

General SQL Server Forums

New to SQL Server Programming

pivot for 3 sql statemnets

Author

Topic

ramyasre
Starting Member

16 Posts

Posted - 2011-04-18 : 02:09:32

i am using the following queries

ALTER proc [dbo].[VOC_RecNPSLocationWiseAVGScore1-Graph]
(
@IN_Month_Value int,@IN_Year_Value int,@Location_Id int
)

AS
BEGIN
Declare @Month_Minus_One int,@Month_Minus_Two int,@Year_Minus_One int,@Year_Minus_Two int,@sentdate datetime;
set @sentdate=convert(datetime,convert(varchar(50),@IN_Month_Value) +'/'+'01'+'/'+ convert(varchar(50),@IN_Year_Value));

set @Month_Minus_One= month(DATEADD(month, -1, @sentdate))
set @Month_Minus_Two= month(DATEADD(month, -2, @sentdate))

if((@IN_Month_Value=1) or (@IN_Month_Value=2))
begin
if(@IN_Month_Value=2)
begin
set @Year_Minus_One= @IN_Year_Value
set @Year_Minus_Two= year(DATEADD(year, -1, @sentdate))
end
else if(@IN_Month_Value=1)
begin
set @Year_Minus_One= year(DATEADD(year, -1, @sentdate))
set @Year_Minus_Two= year(DATEADD(year, -1, @sentdate))
end
end
else
begin
set @Year_Minus_One= @IN_Year_Value
set @Year_Minus_Two= @IN_Year_Value
end

select convert(varchar(20),Month_Value)+'/'+ convert(varchar(20),Year_Value) as datewise,avg(Answer) as a1
from Voc_Trans_Details where Location_Id=@Location_Id and Question_ID in (2,3,4,5,6,7)
and Month_Value=@IN_Month_Value and Year_Value=@IN_Year_Value
group by Month_Value,Year_Value

union

select convert(varchar(20),Month_Value)+'/'+ convert(varchar(20),Year_Value) as datewise,avg(Answer) as a2
from Voc_Trans_Details where Location_Id=@Location_Id and Question_ID in (2,3,4,5,6,7)
and Month_Value=@Month_Minus_One and Year_Value=@Year_Minus_One

group by Month_Value,Year_Value

union

select convert(varchar(20),Month_Value)+'/'+ convert(varchar(20),Year_Value) as datewise,avg(Answer) as a3
from Voc_Trans_Details where Location_Id=@Location_Id and Question_ID in (2,3,4,5,6,7)
and Month_Value=@Month_Minus_Two and Year_Value=@Year_Minus_Two

group by Month_Value,Year_Value

---[VOC_RecNPSLocationWiseAVGScore1-Graph] 9,2010,10201

The output is as follows

Datewise nocolumn
-----------------
7/2010 49
8/2010 52
9/2010 59

now i want ouput as

nocolumn 49 52 59

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 04:32:35

[code]
select avg(case when Month_Value = @IN_Month_Value
and Year_Value = @IN_Year_Value
then Answer
end) as a1,
avg(case when Month_Value = @Month_Minus_One
and Year_Value = @Year_Minus_One
then Answer
end) as a3,
avg(case when Month_Value = @Month_Minus_Two
and Year_Value = @Year_Minus_Two
then Answer
end) as a3
from Voc_Trans_Details
where Location_Id=@Location_Id
and Question_ID in (2,3,4,5,6,7)
and (
(Month_Value = @IN_Month_Value and Year_Value = @IN_Year_Value)
or (Month_Value = @Month_Minus_One and Year_Value = @Year_Minus_One)
or (Month_Value = @Month_Minus_Two and Year_Value = @Year_Minus_Two)
)
~~group by Month_Value,Year_Value~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ramyasre
Starting Member

16 Posts

Posted - 2011-04-18 : 05:43:35

By using the following query it shows null values and the value is showed in 3 rows. Actually i want to use pivot for these statements

quote:

Originally posted by khtan


select 	avg(case when Month_Value = @IN_Month_Value 		
		 and  Year_Value = @IN_Year_Value
		 then Answer
		 end) as a1,
	avg(case when Month_Value = @Month_Minus_One 		
		 and  Year_Value = @Year_Minus_One
		 then Answer
		 end) as a3,
	avg(case when Month_Value = @Month_Minus_Two 		
		 and  Year_Value = @Year_Minus_Two
		 then Answer
		 end) as a3
from 	Voc_Trans_Details 
where 	Location_Id=@Location_Id 
and 	Question_ID in (2,3,4,5,6,7)
and 	(
		(Month_Value = @IN_Month_Value 		and 	Year_Value = @IN_Year_Value)
	or	(Month_Value = @Month_Minus_One 	and 	Year_Value = @Year_Minus_One)
	or	(Month_Value = @Month_Minus_Two 	and 	Year_Value = @Year_Minus_Two)
	)
group by Month_Value,Year_Value

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 05:53:23

oops forgot to remove the group by statement. Edited my last post

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources