Splits one column to multiple column according to data

Question

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

Your question is about pivoting you can just search it in your DBMS and find it ;). — shA.t, Feb 26 '17 at 10:14
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — shA.t, Feb 26 '17 at 10:15

score 0 · Accepted Answer · answered Feb 27 '17 at 06:56

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;

Splits one column to multiple column according to data

1 Answers1