How to change rows to columns in SQL

There are two ways(as per my knowledge) to change rows to columns in SQL

1.Pivot statements

2.Case statements

Case statements
 

If the column names are static, it is quite easy to write.i.e For example say months in a year..
we are completely aware of those colmns names it could only be between jan-dec. In this case we can go ahead and use "Case" directly.

In the below snippet I'm changing one column values to column headers depending upon the month and year.

Select [year],

sum(case when [month]='Jan' then 1 else 0) as 'Jan',

sum(case when [month]='Feb' then 1 else 0) as 'Feb',

sum(case when [month]='Mar' then 1 else 0) as 'Mar',

sum(case when [month]='Apr' then 1 else 0) as 'Apr',

sum(case when [month]='May' then 1 else 0) as 'May',

sum(case when [month]='Jun' then 1 else 0) as 'Jun',

sum(case when [month]='Jul' then 1 else 0) as 'Jul',

sum(case when [month]='Aug' then 1 else 0) as 'Aug',

sum(case when [month]='Sep' then 1 else 0) as 'Sep',

sum(case when [month]='Oct' then 1 else 0) as 'Oct',

sum(case when [month]='Nov' then 1 else 0) as 'Nov',

sum(case when [month]='Dec' then 1 else 0) as 'Dec'        

from table_name

group by [year] 

If the column names are dynamic, we can the same logic above using cursors.

But please do not cursors until there is no other way.

declare @strSQL varchar(4000)
set @strSQL='select [Column_To_group] '
Declare @name varchar(100)
DECLARE descrcursor CURSOR FOR
 

select dept_name from dbo.tblDepartment /* I have the column names in a table*/


OPEN descrcursor

FETCH NEXT FROM descrcursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @strSQL= @strSQL + ' ,sum(case when department='''+replace(@name,'''','''''')+''' then 1 else 0 end ) as ['+@name+']'
FETCH NEXT FROM descrcursor
INTO @name
End
CLOSE descrcursor
DEALLOCATE descrcursor

set @strSQL = @strSQL + ' from table_name
group by [Column_To_group] '
print @strSQL
exec (@strSQL)

PIVOT

The other way is through using PIVOT.
I'm working on this sample, will update it soon.