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
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  as col1,  as col2,  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 (, ,  ) ) as piv -- the pivot table needs a name -- try this in Query Analyzer to see the results.