Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Monday, October 11, 2010 5:31 AM
hi Fredz..
My Final Table is #Example and in that table m adding Addition of 5 Columns but its not working Properly..???
declare @columnheader varchar(max)
Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)')
FROM #Example
DECLARE @query VARCHAR(max)
SET @query = '
SELECT * into fianltable
FROM #Example
PIVOT
(
SUM(Col1+ Col2+ Col3+ Col4 + Col5) In that line error will occur
If m can change this line n taking only **SUM(Col1) **then its Works, but i want addition of
FIVE columns
FOR [MergeValue]
IN (' + @columnheader + ')
)
AS p order by ' + @columnheader + ''
EXECUTE(@query)
select * from fianltable
drop table fianltable
Plz Reply...
If Possible...
NtinK
All replies (6)
Tuesday, October 12, 2010 12:42 PM âś…Answered
Here is non-dynamic sample of what I meant:
use AdventureWorks
select * from (select year(S.OrderDate) as [Order Year],
DATENAME(month, S.OrderDate) as [Order Month],
(SubTotal + TaxAmt) as Total
from Sales.SalesOrderHeader S) src PIVOT(sum(Total) for [Order Month] IN ([January],[February],[March])) pvt
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Monday, October 11, 2010 1:05 PM
PIVOT only allows to pivot based on one column. You have several options:
1. Use derived table to select sum of columns, e.g.
select ... from (select fields, col1+col2+col3+ .. as ColumnToPivot from ...) src PIVOT (SUM(ColumnToPivot) FOR ...) pvt
2. Or alternatively, you can use CASE based pivot.
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Monday, October 11, 2010 1:27 PM
Something to keep in mind is that it is frequently better to go ahead and use the CASE based pivot whenever a pivot becomes more complicated than a 1-column pivot. This is because the CASE based pivot is more general in the scope of problems to which it can be applied. I suggest in this case giving a look at the CASE based pivot.
Tuesday, October 12, 2010 10:02 AM
Thank you..
NtinK
Tuesday, October 12, 2010 10:17 AM
Im used following method Yesterday..
declare @Example1 varchar(max)
declare @Example2 varchar(max)
declare @Example3 varchar(max)
declare @Example4 varchar(max)
declare @Example5 varchar(max)
declare @columns varchar(max)
declare cur_Cols cursor for select distinct MergeValue from #Example
open cur_Cols
fetch next from cur_Cols into @FieldName
while @@Fetch_status = 0
begin
set @Example1 = COALESCE(@Example1 + ',[' + cast(@FieldName as varchar) +'1(COL1)]','[' + cast(@FieldName as varchar) +'1(COL1)]')
set @Example2 = COALESCE(@Example2 + ',[' + cast(@FieldName as varchar) +'2(COL2)]','[' + cast(@FieldName as varchar) +'2(COL2)]')
set @Example3 = COALESCE(@Example3 + ',[' + cast(@FieldName as varchar) +'3(COL3)]','[' + cast(@FieldName as varchar) +'3(COL3)]')
set @Example4 = COALESCE(@Example4 + ',[' + cast(@FieldName as varchar) +'4(COL4)]','[' + cast(@FieldName as varchar) +'4(COL4)]')
set @Example5 = COALESCE(@Example5 + ',[' + cast(@FieldName as varchar) +'5(COL5)]','[' + cast(@FieldName as varchar) +'5(COL5)]')
fetch next from cur_Cols into @FieldName
set @columns = COALESCE(@columns + ',[' + cast(@FieldName as varchar) + ']',
'[' + cast(@FieldName as varchar)+ ']')
end
close cur_Cols
deallocate cur_Cols
update #Example set MergeValue = MergeValue+'1(COL1)'
DECLARE @query VARCHAR(max)
SET @query = 'insert into #ExampleFinal('+ @RowGroup +','+ @Example1 +')
SELECT *
FROM (select ' + @RowGroup + ',MergeValue,COL1 from #Example) as pvt
PIVOT
(
SUM(COL1)
FOR [MergeValue]
IN (' + @Example1 + ')
)
AS p'
EXECUTE(@query)
update #Example set MergeValue = substring(MergeValue,0,len(MergeValue)-6)+'2(COL2)'
SET @query = 'insert into #ExampleFinal('+ @RowGroup +','+ @Example2 +')
SELECT *
FROM (select '+ @RowGroup +',MergeValue,COL2 from #Example) as pvt
PIVOT
(
SUM(COL2)
FOR [MergeValue]
IN (' + @Example2 + ')
)
AS p'
EXECUTE(@query)
update #Example set MergeValue = substring(MergeValue,0,len(MergeValue)-6)+'3(COL3)'
SET @query = 'insert into #ExampleFinal('+ @RowGroup +','+ @Example3 +')
SELECT *
FROM (select '+ @RowGroup +',MergeValue,COL3 from #Example) as pvt
PIVOT
(
SUM(COL3)
FOR [MergeValue]
IN (' + @Example3 + ')
)
AS p'
EXECUTE(@query)
update #Example set MergeValue = substring(MergeValue,0,len(MergeValue)-6)+'4(COL4)'
SET @query = 'insert into #ExampleFinal('+ @RowGroup +','+ @Example4 +')
SELECT *
FROM (select '+ @RowGroup +',MergeValue,COL4 from #Example) as pvt
PIVOT
(
SUM(COL4)
FOR [MergeValue]
IN (' + @Example4 + ')
)
AS p'
EXECUTE(@query)
update #Example set MergeValue = substring(MergeValue,0,len(MergeValue)-6)+'5(COL5)'
SET @query = 'insert into #ExampleFinal('+ @RowGroup +','+ @Example5 +')
SELECT *
FROM (select '+ @RowGroup +',MergeValue,COL5 from #Example) as pvt
PIVOT
(
SUM(COL5)
FOR [MergeValue]
IN (' + @Example5 + ')
)
AS p'
EXECUTE(@query)
NtinK
Tuesday, October 12, 2010 10:19 AM
I didn't Get it..
plz Give me Some Example abt..
" select ... from (select fields, col1+col2+col3+ .. as ColumnToPivot from ...) src PIVOT (SUM(ColumnToPivot) FOR ...) pvt "
this select Statement..
NtinK