SQL: Selecting Columns from Different Rows

There might be times when you have to show values from different records as a row. This could for reporting need and based on the design of the table in the database. Let's say that you have a table that looks like the following:

Name	Tax Type	Number1	Number2
Roy Raphael	Housing	NULL	5000
Sam Murphy	Medical	1000	NULL
Sam Muprhy	Housing	NULL	3000
Roy Raphael	Medical	800	NULL

If you want to display the sum all of the tax types for a single person in a row, one way to do this is by using the case keyword. This article assume that there's only two tax types in the table. So, no query for dynamic number of columns is covered. The expected result could be like the following:

Name	Housing	Medical
Roy Raphael	5000	800
Sam Murphy	3000	1000

By using the case keyword, we can filter which data will be put to which column.

select sum(case when col1 = 'value' then col2 end) as ps-column

In the case above, if the Tax Type value is 'Housing' we put it in pseudo-column named Housing and so we do the same for the Medical tax. The end result of the query might be:

select [name], sum(case when [Tax Type] = 'Housing' then isnull(Cost, 0) end) as Housing, sum(case when [Tax Type] = 'Medical' then isnull(Cost, 0) end) as Medical from taxes group by [name]

The above sql language is a specific sql for SQL server. The isnull function is used so that the sum will just treat null values as 0.