TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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.....
Reply
Answers (
6
)
how to refresh particular div using web service?
Differece between "" and string.empty