SQL Query for union all the months

Question

Hi all, I need to get data to all months for the selected year like this picture this is what i hv done. please send me an answer Declare @ Temp Table ( CompanyID int , ID nvarchar(250) NOT NULL , Name nvarchar(250) , ParentAccID nvarchar(250), ParentAccName nvarchar(250), ParentDisplayID nvarchar(250), DisplayID nvarchar (250), Actual decimal (18,2), Budget decimal (18,2), Classification nvarchar(250), AccLevel int , AccOrder int ); INSERT INTO @ Temp select 4011111111115156122,NEWID(), 'GROSS PROFIT' , '' , 'GP' , '7-0000' , '7-0000' ,0,0, 'GrossProfit' ,1,3 union select 4011111111115156122,NEWID(), 'OPERATING PROFIT' , null , 'OP' , '10-000' , '10-000' ,0,0, 'OperatingProfit' ,1,5 union select 4011111111115156122,NEWID(), 'NET PROFIT / LOSS' , '' , 'P/L' , '11-000' , '11-000' ,0,0, 'NetProfit/Loss' ,1,8 INSERT INTO @ Temp select [CompanyID], [UID], [ Name ], [ParentAccountId], [ParentAccountName], [ParentDisplayID], [DisplayID], 0, 0, [Classification], [AccountLevel], ( case when Classification = 'Income' then 1 when Classification= 'CostOFsales' then 2 when Classification= 'Expense' then 4 when Classification= 'OtherIncome' then 6 when Classification= 'OtherExpense' then 7 else 0 end ) as AccOrder from Account111 where CompanyID=4011111111115156122 AND Classification in ( 'Income' , 'Expense' , 'CostOfSales' , 'OtherIncome' , 'OtherExpense' ) -- Actual for Month update Tupdate set Actual = isnull (( select sum (Activity) from AccountReg where CompanyID = 4011111111115156122 and Year = 2012 and Month =1 and convert ( varchar (250),UID) = Tupdate.ID) ,0) from @ Temp as Tupdate ---- Budget for Month update Tupdate set Budget = isnull (( select sum (Amount) from AccountBud where CompanyID = 4011111111115156122 and Year = 2012 and month =1 and convert ( varchar (250),AccountUID) = Tupdate.ID) ,0) from @ Temp as Tupdate update Tupdate set Actual = (( select Actual from @ Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - ( select Actual from @ Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122)), budget = (( select Budget from @ Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - ( select Budget from @ Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122)) from @ Temp as Tupdate where DisplayID = '7-0000' and CompanyID = 4011111111115156122 update Tupdate set Actual = (( select Actual from @ Temp where DisplayID = '7-0000' AND CompanyID = 4011111111115156122 ) - ( select Actual from @ Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122)), budget = (( select Budget from @ Temp where DisplayID = '7-0000' AND CompanyID = 4011111111115156122 ) - ( select Budget from @ Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122)) from @ Temp as Tupdate where DisplayID= '10-000' and CompanyID = 4011111111115156122 update Tupdate set Actual = (( select Actual from @ Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - ( select Actual from @ Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122) - ( select Actual from @ Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122) + ( select Actual from @ Temp where DisplayID = '8-0000' and CompanyID = 4011111111115156122)- ( select Actual from @ Temp where DisplayID = '9-0000' and CompanyID = 4011111111115156122) ), budget = (( select Budget from @ Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - ( select Budget from @ Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122) - ( select Budget from @ Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122) + ( select Budget from @ Temp where DisplayID = '8-0000' and CompanyID = 4011111111115156122)- ( select Budget from @ Temp where DisplayID = '9-0000' and CompanyID = 4011111111115156122)) from @ Temp as Tupdate where DisplayID= '11-000' and CompanyID = 4011111111115156122 select * from @ Temp order by AccOrder, AccLevel,ID thank you

Kapil Singh Kumawat · Answer

The output that you want can be achieved using Pivot in sql server. Please check this link for information and example about pivot - https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Kasuni Abegunawardana · Answer

As u can see here i am using one temporary table and getting records into the selected month and year. Here using Update one i am giving the parameters to selected year and month. That's completed but now i want to get the data for all the months. If I have selected 2015 with "All" as month it should give me all the month Jan - Dec. Can u say how to update that query

Nitin Sontakke · Answer

The output that you have pasted can be obtained by using pivot SQL Server keyword. Have you already looked into that? The query you posted is too complex to look into. Provide some real simple sample data and so as to work on it.

Nitin Sontakke · Answer

I think you have made it complex unnecessarily. There are simple ways to achieve the same result, I think. If you give some simple sample data in script format (so that I don't have to type it all again) and an expected output, may be, I can provide you the query using pivot. OR if you just google word pivot in sql server, you will the examples online.