tsql - Creating a complex pivot table in T-SQL -

tsql - Creating a complex pivot table in T-SQL -

i using sql server 2008 r2 , t-sql. these abbreviated examples.

my table/view has next fields

table/view reportid, unitname, unitid, casedefinitionid, casedefinition, dateofdelivery, year(dateofdelivery) [year], month(dateofdelivery) [month], datename(m,dateofdelivery) [month name]

and target study is:

unit | case type 1| case type 2| case total| unit | 36| 40| 76| 2013| 20| 18| 38| jan| 10| feb| 10| 2014| 16| mar| 8| dec| 8| unit b | 12| 2013| 12| jan| 6| may| 6| grand total| 48|

the month rows can empty, null content fine. illustration study incomplete aid clarity.

progress far next query:

select * ( select pct.[year], pct.unitname [unit], pct.unitid, pct.casedefinition [case definition] pivotbase pct ) p1 pivot ( count(unitid) [case definition] in ([case type 1],[case type 2]) ) p2

this produces next table

year|unit |case type 1|case type 2| 2013|unit | 20 | 18| 2014|unit | 16 | 22| 2013|unit b | 6 | 8| 2014|unit b | 6 | 8|

there no need dynamic sql

my reading far has covered many options still have no thought go next. how produce required report.

you can there pivoting on month , year , using rollup

with info (select * (select dateadd(month, datediff(month, 0, pct.dateofdelivery), 0 ) monthyear, pct.unitid, pct.unitname [unit], pct.casedefinition [case definition] pivotbase pct) p1 pivot ( count(unitid) [case definition] in ([case type 1], [case type 2]) ) p2), rollup (select month(monthyear) month, unit unitx, year(monthyear) year, sum([case type 1]) [case type 1], sum([case type 2]) [case type 2], grouping(unit) gunit, grouping(month(monthyear)) gm, grouping(year(monthyear)) gy info grouping unit, year(monthyear), month(monthyear) rollup) select coalesce(cast(month varchar), cast(year varchar), unitx, 'grand total') unit, [case type 1], [case type 2] rollup order gunit, unitx, year, gm desc, month

sqlfiddle

sql tsql sql-server-2008-r2