Displaying the results of a query in matrix format (pivot table)
Thursday, May 31st, 2007Today I came across an unusual request, I had to query the database and return the values in a pivot table format (variable columns). That’s kind of easy to do if you are using Sql Reporting, but I had never had to display a gridview in this format. After some research, I was able to find and article on Uma Chandar’s site doing just this: here
create table #tb (record_id int, obj_id char(4), reference char(10))
insert #tb values(1, ‘Btn1′, ‘forward’)
insert #tb values(1, ‘Btn2′, ‘backward’)
select
record_id,
min(case when obj_id = ‘btn1′ then reference end) as btn1,
min(case when obj_id = ‘btn2′ then reference end) as btn2
from #tb
group by record_id
I hope this helps.