Riddhi Valecha

Riddhi Valecha

  • 420
  • 3.3k
  • 411.8k

Dynamic Columns in SQL Query - URGENT PLZZ HELP ME OUT

Dec 21 2013 7:57 AM
 Hi...

I have executed the following stored procedure (helped by Mr. Jignesh Trivedi)

Create table #temp
(ID int,
Company  varchar(25),
Location  varchar(25),
Date date,
Count int,
Status varchar(25))

insert into #temp values(1,'TCS','Mumbai','1-1-2010',1,'Clear'),
 (2,'Zensar','Hydrabad','2-2-2010',2,'Rejected'),
 (3,'Infosys','Pune','3-3-2011',4,'Clear'),
 (4,'IBM','Pune','4-4-2011',3,'Clear'),
 (5,'Microsoft','Singapore','5-5-2012',5,'Rejected'),
 (6,'Google','US','6-6-2013',4,'Rejected')
 
 declare @columnscsv varchar(MAX)
 declare @Sumcolumnscsv varchar(MAX)
 declare @Sumcolumnscsvho varchar(MAX)
 DECLARE @sql varchar(MAX)
 Select @columnscsv = COALESCE(@columnscsv + '],[','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear,Sum([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 print 'isnull([' + @Sumcolumnscsvho + '],0) as Total'
 
SET @sql = '
SELECT Company, Location,Status,sum([' + @Sumcolumnscsv +', isnull([' + @Sumcolumnscsvho + '],0) as Total
FROM (
  select Company,Location, Datename(m,Date) + cast(year(Date) as varchar) + Status as m,Count, Status from #temp
) as A
PIVOT
(
  SUM(Count)
  FOR m IN (' + '[' + @columnscsv + ']' + ')
)AS pivot1 GROUP BY
  ROLLUP((Company, Location,Status,' + '[' + @columnscsv + ']' + '))'
--print @sql
exec(@sql)

----------------------

Here,

I am getting 1 column "Total". Instead of this, I need 2 Total Colums -

1)Total Column name as "ClearTotal". Here I need the Total count of all the clear months.
i.e January2013Clear + march2013Clear

2) Total Colummn name as "RejectedTotal". Here, I need the total count of all rejecyed months.
i.e. January2013Rejected + March2013Rejected.

Please help me out.... Its dam urgent.......

Also, I need the total of these 2 columns - "ClearTotal" and "RejectedTotal".

Please help !!

Thanks a ton in advance.....


Answers (6)