sqlsql-serversql-server-2008-r2pivot

Splits one column to multiple column according to data

I have a table which contains data of std attendance of one year

AttID Present absent. leave sick month StdRegNo
1. 23 1 0 0 JAN. 1
2. 25 0 0 0 JAN. 2
3. 23 0 0 0 MAR. 1
4. 21 3 0 1 MAR. 2
SO ON.......

I want result in such a view as bellow:

StdReq month P A L S month P A L S
1. Jan. 23 1 0 0 Mar 23 0 0 0
2. Jan. 25 0 0 0 Mar 21 3 0 1

I need this view for 12 months how can I do this? please help me

Solution

  • You can use a query like this:

    select StdRegNo
    -- January info
    ,max(case when [month] = 'JAN' then Present end) JAN_P
    ,max(case when [month] = 'JAN' then [absent] end) JAN_A
    ,max(case when [month] = 'JAN' then leave end) JAN_L
    ,max(case when [month] = 'JAN' then sick end) JAN_S
    -- March info
    ,max(case when [month] = 'MAR' then Present end) MAR_P
    ,max(case when [month] = 'MAR' then [absent] end) MAR_A
    ,max(case when [month] = 'MAR' then leave end) MAR_L
    ,max(case when [month] = 'MAR' then sick end) MAR_S
    -- And so on ...
from yourTable
group by StdRegNo;