Need help getting results to one row

Transact-SQL
1

Hello,

I have the following SQL query:

select hr_pe_id
--,pyx_qtd03
--,pyx_qtd04
,case when pyx_yy = '2012' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2012 July - december'
,case when pyx_yy = '2013' then sum (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END AS '2013 july - december'
,case when pyx_yy = '2014' then sum (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END AS '2014 july - december'
,case when pyx_yy = '2015' then sum (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END AS '2015 july - december'
,case when pyx_yy = '2016' then sum (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END AS '2016 july - december'
,case when pyx_yy = '2017' then sum (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END AS '2017 july - december'
,case when pyx_yy = '2013' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2013 jan - june'
,case when pyx_yy = '2014' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2014 jan - june'
,case when pyx_yy = '2015' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2015 jan - june'
,case when pyx_yy = '2016' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2016 jan - june'
,case when pyx_yy = '2017' then sum (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END AS '2017 jan - june'
from PYX_XTD_DTL

where PYX_YY in ('2012','2013','2014','2015','2016','2017')
and PYX_no = 6002
group by hr_pe_id, pyx_qtd03, pyx_qtd04,pyx_yy
order by hr_pe_id;

The results come turn out with each year on it's own row, but I would like the results to be on one row. See pics attached.

SQL%20results
SQL results.png1289×252 6.67 KB

I can't post another pic yet, but from the pick above, E00018 should have 1 line with all the numbers going straight across.

Thanks.

2

what happens if you remove

, pyx_qtd03, pyx_qtd04,pyx_yy

from the group by?

3

I actually have that line commented out. Yeah it's not needed.

4 
select hr_pe_id
--,pyx_qtd03	
--,pyx_qtd04
,sum(case when pyx_yy = '2012' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2012 July - december'
,sum(case when pyx_yy = '2013' then (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END) AS '2013 july - december'
,sum(case when pyx_yy = '2014' then (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END) AS '2014 july - december'
,sum(case when pyx_yy = '2015' then (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END) AS '2015 july - december'
,sum(case when pyx_yy = '2016' then (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END) AS '2016 july - december'
,sum(case when pyx_yy = '2017' then (pyx_qtd03 + pyx_qtd04)/100 ELSE 0 END) AS '2017 july - december'	
,sum(case when pyx_yy = '2013' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2013 jan - june'
,sum(case when pyx_yy = '2014' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2014 jan - june'
,sum(case when pyx_yy = '2015' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2015 jan - june'
,sum(case when pyx_yy = '2016' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2016 jan - june'
,sum(case when pyx_yy = '2017' then (pyx_qtd01 + pyx_qtd02)/100 ELSE 0 END) AS '2017 jan - june'
from PYX_XTD_DTL

where PYX_YY in ('2012','2013','2014','2015','2016','2017')
and PYX_no = 6002
group by hr_pe_id
order by hr_pe_id;
1 Like
5

Thanks ScottPletcher! Crazy that the group by was the culprit.