in Programming |

SQL Pivot

A co-worker asked me about transposing a question response table. I came from a SQL 2000 background, so I was excited to play with the PIVOT command which is available in SQL 2005 and 2008.  It’s a common use case to transpose row-based data into columns. Sometimes it just makes more sense to people to view data this way.

— first, let’s set up a sample table

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
drop table response
go
Create table response (
-- no primary key
question_fk int, -- some foreign key to another table
response_num int, -- some column to pivot on
response_text varchar(100), -- some text to show in the table
response_type char(1) -- another foreign key
)
go
insert into response values (1, 1, 'response 1.1', 'a')
insert into response values (1, 2, 'response 1.2', 'a')
insert into response values (1, 3, 'response 1.3', 'a')
 
insert into response values (2, 1, 'response 2.1', 'b')
insert into response values (2, 2, 'response 2.2', 'b')
insert into response values (2, 3, 'response 2.3', 'b')
 
-- then, we use a pivot command to transpose the table.
 
select
 
-- some columns will just be returned as is
response_type,
question_fk,
-- other columns will pivot based on values and adopt the values as
-- their column names. these columns may be aliased into more appropriate names
[1] as col1,
[2] as col2,
[3] as col3 from
( select question_fk, response_num, response_text, response_type from response ) as src
pivot (
min ( response_text ) -- we use an aggregate for the pivot
for response_num in ([1], [2], [3] )
) as piv -- the pivot table needs a name
 
-- try this in Query Analyzer to see the results.