sql - Using Pivot with more than one column using sum? -

sql - Using Pivot with more than one column using sum? -

i have query:

select dept,csedept_name,january,february,march,april,may,june,july,august,september,october,november,december (select cast(employeedept int) dept, round(avg(case when rating1>0 cast(rating1 float) else null end), 2) q1, round(avg(case when rating2>0 cast(rating2 float) else null end), 2) q2, round(avg(case when rating3>0 cast(rating3 float) else null end), 2) q3, round(avg(case when rating4>0 cast(rating4 float) else null end), 2) q4, round(avg(case when rating5>0 cast(rating5 float) else null end), 2) q5, count(*) 'totalstars',month_cse= datename(month,execoffice_date),year_cse =year(execoffice_date) csereduxresponses execoffice_status = 1 , employeedept =17 grouping employeedept,month(execoffice_date),year(execoffice_date),datename(month,execoffice_date) ) r bring together csereduxdepts d on d.csedept_id = r.dept , d.csedept_id=17 pivot( sum(q1) [month_cse] in ( [january],[february],[march],[april],[may],[june],[july],[august], [september],[october],[november],[december] )) pvt

which gets average in each month depending on department. in query above im getting sum 'q1' , displaying right number month , section displaying each month in 1 row , can show 'q1' when show q1-q5.

i may taking long/wrong way this, maybe using pivot wrong way go.

is there way able add together q1-q5 , show on corresponding month?

i made http://sqlfiddle.com/#!3/05390/1

how query:

sql fiddle

query 1:

;with years (select distinct year(execoffice_date) y csereduxresponses), months (select 'january' m, 1 n union select 'february', 2 n union select 'march', 3 n union select 'april', 4 n union select 'may', 5 n union select 'june', 6 n union select 'july', 7 n union select 'august', 8 n union select 'september', 9 n union select 'october', 10 n union select 'november', 11 n union select 'december', 12 n), cse (select cast(employeedept int) dept, round(avg(case when rating1>0 cast(rating1 float) else null end), 2) q1, round(avg(case when rating2>0 cast(rating2 float) else null end), 2) q2, round(avg(case when rating3>0 cast(rating3 float) else null end), 2) q3, round(avg(case when rating4>0 cast(rating4 float) else null end), 2) q4, round(avg(case when rating5>0 cast(rating5 float) else null end), 2) q5, count(*) 'totalstars', year(execoffice_date) year_cse, month(execoffice_date) m_cse csereduxresponses grouping employeedept, month(execoffice_date), year(execoffice_date) ) select csedept_id, d.csedept_name, years.y, months.m, q1, q2, q3, q4, q5, totalstars years cross bring together months cross bring together csereduxdepts d left bring together cse on months.n = cse.m_cse , d.csedept_id = cse.dept d.csedept_id = 17 order years.y, months.n, csedept_id, csedept_name

results:

| csedept_id | csedept_name | y | m | q1 | q2 | q3 | q4 | q5 | totalstars | |------------|---------------|------|-----------|--------|--------|--------|--------|--------|------------| | 17 | section 17 | 2014 | jan | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | feb | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | march | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | apr | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | may | 2.83 | 4.5 | 3.67 | 1.75 | 1 | 6 | | 17 | section 17 | 2014 | june | 2.33 | 4 | 3.33 | 2 | 1 | 3 | | 17 | section 17 | 2014 | july | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | august | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | september | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | oct | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | nov | (null) | (null) | (null) | (null) | (null) | (null) | | 17 | section 17 | 2014 | dec | (null) | (null) | (null) | (null) | (null) | (null) |

you can comment lastly where d.csedept_id = 17 clause retrieve details departments. not think displaying q1, q2, q3, q4, q5, totalstars in separate columns every month wise. end 60 columns in case

sql sql-server-2008