CODEWITHSUNDEEP
sqlsql-server
P Kernel
P Kernel

Reputation: 247

Iterate within View in SQL Server

I am looking for creating a SQL view where I have data as per the below screenshot

enter image description here

I want to convert rows into columns but based on # of Months. For Example, if # Month is 3 then add value to Jan, Feb, and Mar, and so on.

enter image description here

Upvotes: 0

Views: 206

Answers (2)

Chuma
Chuma

Reputation: 739

The solution should look like this:

  1. First create a table or function to return your cross reference of Months to their abbreviations. Going simple, it looks like:
Create Table MonthDef (
  MonthID int,
  MonthAbbr varchar(3)
);
  1. Join the table above with your salary to convert your salary data to have month detail granularity
with SalaryMonthDetail as
(
 select SalaryID, Salary, MonthAbbr from Salaries s inner join MonthDef m on s.months>=m.MonthID
)
  1. Pivot the result and convert those rows to columns
select *
from SalaryMonthDetail
PIVOT(
    Min(Salary) 
    FOR MonthAbbr IN (
        [Jan], 
        [Feb], 
        [Mar], 
        [Apr], 
        [May], 
        [Jun], 
        [Jul],
        [Aug],
        [Sep],
        [Oct],
        [Nov],
        [Dec]
    )
) AS pivot_sal;
  1. Put 2 to 3 in a view and select like seen here

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Something like this:

select ID
       ,case when Months >= 1 then Salary else null end Jan
       ,case when Months >= 2 then Salary else null end Feb
       ,case when Months >= 3 then Salary else null end Mar
       ,case when Months >= 4 then Salary else null end Apr
       ,case when Months >= 5 then Salary else null end May
from some_table

Upvotes: 1

Related Questions