12.5.09

Group by month in SQL Server

Group by month's in SQL server:

Lets see some peculiar example, I need to group the data based on the id and grouped by month. The data is located in the rows and I need to populate the data in columns. How to achieve it?

DROP TABLE SAMPLE
create table sample(id int, BusId varchar(5), amt1 decimal(10,2), amnt2 decimal(10,2), dat datetime)
insert into sample values(1, 'A1234', 100,200,'2008-12-03')
insert into sample values(2, 'A1234', 200,200,'2008-12-30')
insert into sample values(3, 'A1234', 300,200,'2008-12-31')
insert into sample values(4, 'A1234', 400,200,'2009-01-03')
insert into sample values(5, 'A1234', 500,200,'2009-01-22')
insert into sample values(6, 'A1234', 600,200,'2009-02-13')
select * from sample




select BUSID,AMT1,AMNT2,CONVERT(VARCHAR(100),MONTH(DAT))+' '+CONVERT(VARCHAR(100),YEAR(DAT)) AS VAL from sample


SELECT BUSID, SUM(AMT1) as AMOUNT1,SUM(AMNT2) AS AMOUNT2 ,DAT1 FROM
(
select BUSID,AMT1 ,AMNT2,CONVERT(CHAR(4), dat, 100) + CONVERT(CHAR(4), dat, 120) AS DAT1 from sample
)T
GROUP BY DAT1,BUSID




Thanks and Regards,

Venkatesan Prabu .J

No comments:

Post a Comment