12 Dec
2008

SQL Pivot

Category:UncategorizedTag: :

This is Jason who is continuing to masquerade as a guest.

I’m going to let you into a dirty little secret…I’ve written a lot of SQL during my career. This admission almost sounds like heresay in these days of DDD, BDD etc. but it’s still an incredibly useful skill to have. Anyway a lot of our development is performed in an environment where OR/M tools such as NHibernate are not an option so we need to rely on such traditional techniques more often than I would like.

Today someone came to me and said "So I have a route. And a route can have jobs. And each job has a status. I need to show for each route the number of jobs that have a particular status and which don’t have the status".

What they are really asking for is to pivot the data – the rows need to be converted to columns. The technique that I employed to solve this problem uses a combination of a case statement and an aggregate function to pivot the data. More explicitly the case statement is used to bucket the data and the aggregate function to do something to that bucket. Normally I would use a COUNT but the RDBMS in question (SQL Anywhere) throws a warning when a null is aggregated so I used SUM instead.

What was interesting when I looked at how I presented my solution to my colleague was that the format is not too dissimilar to what I might do with a more fully featured language than SQL (yes I know there’s no tests but it’s pretty close to a specification as it’s a paint by numbers solution). Anyway here’s the SQL:

create table route (id int)

create table job (id int, route_id int, status varchar(1))

insert into route (id) values (1)
insert into route (id) values (2)

insert into job (id, route_id, status) values (1, 1, ‘A’)
insert into job (id, route_id, status) values (2, 1, ‘B’)
insert into job (id, route_id, status) values (3, 1, ‘A’)
insert into job (id, route_id, status) values (4, 2, ‘A’)
insert into job (id, route_id, status) values (4, 2, ‘B’)
insert into job (id, route_id, status) values (5, 2, ‘C’)

select
  route_id,
  sum(case when status = ‘A’ then 1 else 0 end) sum_a,
  sum(case when status <> ‘A’ then 1 else 0 end) sum_not_a
from
  route r
  inner join job j on r.id = j.route_id
group by
  route_id
order by
  route_id

2 thoughts on “SQL Pivot

Comments are closed.