Endy Mooduto Endy Mooduto - 5 months ago 19
SQL Question

SQL Server : create view or procedure?

Currently I'm working on a project with sql server database on it. I have a problem with some query. i have a table:

Current table:

Month | Year | EmpID | Salary
------------------------------------
1 2016 123 100
2 2016 123 120
3 2016 123 110
4 2016 123 150
1 2016 456 200
2 2016 456 210
3 2016 456 225
4 2016 456 220


And I want to create something with result like this:

After query table:

EmpID | Salary1 | Salary2 | Salary3 | Salary4
123 100 120 110 150
456 200 210 225 220


Note : naming salary table based on month table so there will be Salary12

What query should I type? Should I create a view or a stored procedure for this?

Answer

Your choices are a view, stored procedure, or stored function. I see not reason to use a stored procedure -- I think a result set is much more useful when you can use it in another query.

You can use either pivot or conditional aggregation for the result. As a view:

create view v_current as
     select c.empid, 
            max(casewhen c.month = 1 then salary end) as salary_01,
            max(casewhen c.month = 2 then salary end) as salary_02,
            . . .
            max(casewhen c.month = 12 then salary end) as salary_12
     from current c
     where c.year = year(getdate())
     group by c.empid;

Note: monthand year are keywords in SQL Server so they are very bad choices for column names. You should find another name for these columns.