pivot example sp

USE [Relishdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_salestransaction_monthlyrep]    Script Date: 02/01/2011 17:41:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_salestransaction_monthlyrep] --1,1
(
@locationId int,
@monthid int
)--'cibertowers', 1
as
begin


declare @date datetime
set  @date = (select top 1 Trasdate from salestransaction where moncol=@monthid)

DECLARE @lastDate DATETIME
SET @lastDate = @date



 set @lastDate =  (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@lastDate)+1,0))LastDay_AnyMonth)

DECLARE @firstdate DATETIME
SEt @firstdate = @date

--Get First Day of a Month Using SQL Queryset
set @firstdate = (SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@firstdate))-1),DATEADD(mm,0,@firstdate)))



select  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] , 'Espresso' as productname,  (select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Espresso from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate ) p pivot (sum(Espresso) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt
union all
select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Cappuccino' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Cappuccino from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Cappuccino) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt
union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Latte' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Latte from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Latte) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt
 union all

 select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Tea' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Tea from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Tea) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Lemon tea' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Lemontea from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Lemontea) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt

union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Milk' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Milk from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Milk) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Green tea' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Greentea from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Greentea) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt

union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Boost' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Boost from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Boost) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Horlicks' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Horlicks from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Horlicks) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'Dialy milk' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,Dailymilk from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(Dailymilk) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'chocomilk' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,chocolatemilk from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(chocolatemilk) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


union all

select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],'soop' as productname,(select convert(varchar(50),@lastDate,106)) as Lastdate, (select convert(varchar(50),@firstdate,106)) as Firstdate
from (select daycol,soop from salestransaction where locationId=@locationId and Trasdate between @firstdate and @lastDate) p pivot (sum(soop) for daycol in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))as pvt


end


exec [dbo].[sp_salestransaction_monthlyrep] 1,2