Feeds:
Posts
Comments

Archive for December, 2015


-- 1) Get your source data data. This is the result that contains data that you would like to pivot

if object_id('tempdb..##t') is not null
drop table ##t

select *
INTO ##t -- you will need to store it in a Global temp table
from TreeExample
-- ... some joins and conditoiins ...
-- 2) To dynamically populate columns create the variables for it

declare @pivotedfields varchar(max), @pivotedfieldsSummed varchar(max)

-- i) The below is to use when pivoting itself

SELECT @pivotedfields = COALESCE(@pivotedfields + ',[' + MonthYear + ']','[' + MonthYear+ ']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

-- ii) there are times when AFTER the pivot the whole result might need to be grouped again for each unique IDs (of some kind) and the NULLs across disappears because data are stacked on the same rows.

SELECT @pivotedfieldsSummed = COALESCE(@pivotedfieldsSummed + ',sum(isnull([' + MonthYear + '],0)) [' + MonthYear + ']','sum(isnull([' + MonthYear+ '],0)) [' + MonthYear +']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

--select @pivotedfields, @pivotedfieldsSummed -- test
declare @sql as varchar(max)
set @sql =
'SELECT convert(char(10),min(CountStartDate),103) as CountStartFrom, -- The first day the count was started
IsTreeOrPlant,
TreeNumber,'
+ @pivotedfieldsSummed + --- we are using hte Summed varialbe here
'
FROM
(
select * from ##t -- this is the source data
) as s
PIVOT
(
SUM(TreeLeaves)
FOR MonthYear IN (' + @pivotedfields + ') -- this is where the first pivot variable goes.
)AS p
group by IsTreeOrPlant, TreeNumber -- We specify what we want it grouped by
order by CountStartFrom' -- this is optional but we acn sort with any one of hte fields

--print @sql -- test
exec (@sql)

Read Full Post »