alex paolo alex paolo - 5 months ago 10
SQL Question

how to view sql query horizontally using pivot from 1 table

table.table

rows of my table. for january

january result

rows for february

february result

My sample query

select id2,
(select budget from tblmonth where id2='1' and month='January' and groups='CCARE') as jbud1,
(select actual from tblmonth where id2='1' and month='January' and groups='CCARE') as jact1,
(select variance from tblmonth where id2='1' and month='January' and groups='CCARE') as jvar1,
(select [percent] from tblmonth where id2='1' and month='January' and groups='CCARE') as jper1,

(select budget from tblmonth where id2='2' and month='February' and groups='CCARE') as fbud2,
(select actual from tblmonth where id2='2' and month='February' and groups='CCARE') as fact2,
(select variance from tblmonth where id2='2' and month='February' and groups='CCARE') as fvar2,
(select [percent] from tblmonth where id2='2' and month='February' and groups='CCARE') as fper2


from tblmonth where groups='CCARE' and id2='1' and month='January'


my problem with this query is it returns duplicate values.
duplicate

i want to achieve is.

the columns budget, actual, variance, percent from each month will be this display in a single result. like this in the image.

want to achieved

thanks in advance :) im only new to sql and im using a sql server 2014.

Answer

Try this:

select t1.id2, t1.budget,t1.actual,t1.variance,t1[percent]
,t2.budget,t2.actual,t2.variance,t2.[percent] 
from tblmonth t1 inner join 
 (select * from tblmonth where groups='CCARE' and month='February' ) t2
 on t1.id2 = t2.id2 and t1.year = t2.year
 where t1.groups='CCARE' and t1.month='January' 
Comments