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:

NameTax Type Number1Number2
Roy Raphael HousingNULL5000
Sam Murphy Medical1000NULL
Sam Muprhy HousingNULL3000
Roy Raphael Medical800NULL

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:

NameHousingMedical
Roy Raphael 5000800
Sam Murphy 30001000


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.


0 comments:

top