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

Answer 1

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;