Dynamic Columns in SQL Query - URGENT PLZZ HELP ME OUT

Question

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.....

Jignesh Trivedi · Answer

hi Ok cool.... try... 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(mDate) + cast(year(Date) as varchar) + 'Clear][' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + ']0) + isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]0) + isnull([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'Sum([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'ClearSum([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) --print 'isnull([' + @Sumcolumnscsvho + ']0) as Total' Declare @SumClearcolumnscsv varchar(max) Select @SumClearcolumnscsv = COALESCE(@SumClearcolumnscsv + '+ isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]0) ' from #temp group by Dateyear(Date) --print 'isnull([' +@SumClearcolumnscsv Declare @SumRejectcolumnscsv varchar(max) Select @SumRejectcolumnscsv = COALESCE(@SumRejectcolumnscsv + ' + isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected]0) ' from #temp group by Dateyear(Date) --print @SumRejectcolumnscsv SET @sql = ' SELECT Company LocationStatussum([' + @Sumcolumnscsv + ' SUM(Total) as Total SUM(ClearTotal) as ClearTotal SUM(RejectTotal) as RejectTotal from ( SELECT Company LocationStatus [' + @columnscsv + ']  isnull([' + @Sumcolumnscsvho + ']0) as Total isnull([' +@SumClearcolumnscsv + ' as ClearTotal isnull([' +@SumRejectcolumnscsv + ' as RejectTotal FROM ( select CompanyLocation Datename(mDate) + cast(year(Date) as varchar) + Status as mCount Status from #temp ) as A PIVOT ( SUM(Count) FOR m IN (' + '[' + @columnscsv + ']' + ') )AS pivot1 ) A GROUP BY ROLLUP((Company LocationStatus' + '[' + @columnscsv + ']' + ' [Total] ))' exec(@sql) hope this will help you. do not forgot to mark it as answer if helped.

Riddhi Valecha · Answer

Hi... Please help.... to get Column-wise total also.... Company Location April2013Clear April2013Rejected May2013Clear May2013Rejected TotalClear TotalRejected A H 1 2 1 2 1+1=2 2+2=4 B G 2 3 2 3 2+2=4 3+3=6 C F 3 4 3 4 3+3=6 4+4=8 6 9 6 9 6+4+2=12 4+6+8=18 PLZZ/..

Riddhi Valecha · Answer

Hi... Please help me out once again Mr. Jignesh Trivedi ... Please help me out to get this column-wise total aslo.... About that dynamic query... yes it is done.... issue resolved.... Thanks a ton for that..

Jignesh Trivedi · Answer

hi have you solve your dynamic query problem?

Riddhi Valecha · Answer

Hi... THANKS A TON ONCE AGAIN.............. 1 more query.... I am getting the output as follows - Company Location March2010Clear March2010Rejected April2010Clear April2010Rejected Total TotalClear TotalRejected A F 1 2 1 2 1+2+1+2=6 2 4 B E 2 3 2 3 10 4 6 C D 3 4 3 4 14 6 8 6 9 6 9 0 0 0 How do I get 30 on last cell of column Total instead of 0???

Jignesh Trivedi · Answer

hi try following query it might help you. 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(mDate) + cast(year(Date) as varchar) + 'Clear][' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + ']0) + isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]0) + isnull([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'Sum([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'ClearSum([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) --print 'isnull([' + @Sumcolumnscsvho + ']0) as Total' Declare @SumClearcolumnscsv varchar(max) Select @SumClearcolumnscsv = COALESCE(@SumClearcolumnscsv + '+ isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]0) ' from #temp group by Dateyear(Date) --print 'isnull([' +@SumClearcolumnscsv Declare @SumRejectcolumnscsv varchar(max) Select @SumRejectcolumnscsv = COALESCE(@SumRejectcolumnscsv + ' + isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected]0) ' from #temp group by Dateyear(Date) --print @SumRejectcolumnscsv SET @sql = ' SELECT Company LocationStatussum([' + @Sumcolumnscsv +' isnull([' + @Sumcolumnscsvho + ']0) as Total isnull([' +@SumClearcolumnscsv+ ' as ClearTotal isnull([' +@SumRejectcolumnscsv + ' as RejectTotal FROM ( select CompanyLocation Datename(mDate) + cast(year(Date) as varchar) + Status as mCount Status from #temp ) as A PIVOT ( SUM(Count) FOR m IN (' + '[' + @columnscsv + ']' + ') )AS pivot1 GROUP BY ROLLUP((Company LocationStatus' + '[' + @columnscsv + ']' + '))' --print @sql exec(@sql)