My table has this structure
subcode date rol1 rol2 rol3 rol4 rol5 rol6 upto rol60
--------------------------------------------------------------
mc1603 12/03/2011 p p a p p p a
mc1604 12/03/2011 p p a p p p a
mc1605 12/03/2011 p p a p p p a
mc1606 12/03/2011 p p a p p p a
here
p=present
a=absent
this table will be change into
rollno mc1603 mc1604 mc1605 mc1606 date
-------------------------------------------------
rol1 p p p p 12/03/2011
rol2 p p p p 12/03/2011
rol3 p p a p 12/03/2011
You have to pivot the subcode column, and then unpivot the other roll columns.
The pivoted table would be a derived table inside the unpivot statement.
This can be done, it's called a table transpose. On SQL Server it requires building a dynamic SQL statement and executing it using the EXEC statement.
Here is how you would do this with both an
unpivot
and thenpivot
:Static query:
You can also do this with Dynamic SQL but you will need to get the list of columns first before you can
unpivot
and thenpivot
:Dynamic query:
Results: