Row to Column - T SQL Challenge (Group by month)

This one is pretty interesting article among my best articles in this blog.


In this article am trying to do some T-SQL tweaks to achieve a complex functionality. Let me stop exaggregating the things and proceed with my problem and solutions for the same.



Problem Statement:

Its a typical row to column problem in SQL Server (Its my favourite as usual) .


Am having the person whose ratings will be changing differently in a month. I want to sum all the ratings of a person for a month providing the input month is given to you Considering July and august as the month.




If you read further more on the sample, you can understand, what am trying to achieve in SQL Server.



create table venkat1(id int, rating int, dat datetime,auditrecord int)
insert into venkat1 values(1,1,'7/1/2006',1)
insert into venkat1 values(1,3,'7/1/2006',2)
insert into venkat1 values(1,5,'7/2/2006',3)
insert into venkat1 values(1,2,'8/10/2006',4)
insert into venkat1 values(2,4,'8/7/2006',1)
select * from venkat1

Now, am trying to use row_number concept to fetch the ratings of the individual based on the month. After getting this input, I am inserting the same to a temporary table.

Below is the query for the same,

drop table #subatable
select id, rating, dat,month(dat) as mont
into #subatable from
(
select id,rating,row_number() over( partition by id,month(dat) order by auditrecord desc)
as rownum,dat
,month(dat) as mont
from venkat1
)t
where rownum=1
select * from #subatable

Now, a simple row to column conversion with input parameter as month.

Below is the query for the same,

select distinct a.id, month1 =(select rating from #subatable where mont=7 and id=a.id),
month2 =(select rating from #subatable where mont=8 and id=a.id)
from #subatable a

Happy Learning!!!

Thanks and Regards,

Venkatesan Prabu .J