SQL Server PIVOT

im fairly sure i can use the PIVOT function to solve my issue, but i just can't figure it out. Any assistance would be greatly appreciated.

So i have a table that looks like

create table Answer (
id int,
question_id int,
user_id int,
answer varchar(1025))

and i would like to write a query that returns a result s开发者_运维问答et in the following:

user_id, question_1, question_2, question_3
1,       'answer1',  'answer2',  'answer3'
2,       'answer1',  'answer2',  'answer3'
n,       'answer1',  'answer2',  'answer3'

is this even possible?

TIA

Assumming this input in your table:

insert into answer SELECT 1, 1, 123, 'Answer ZZZZ'
insert into answer SELECT 2, 2, 123, 'Answer AAAA'
insert into answer SELECT 3, 3, 123, 'Answer BBBB'
insert into answer SELECT 4, 1, 345, 'Answer CCCC'
insert into answer SELECT 5, 2, 345, 'Answer DDDD'
insert into answer SELECT 6, 1, 678, 'Answer EEEE'
insert into answer SELECT 7, 2, 678, 'Answer FFFF'
insert into answer SELECT 8, 3, 678, 'Answer SSSS'
insert into answer SELECT 9, 3, 999, 'Answer RRRR'

You can do something like this:

SELECT user_id, [1] as Answer1, [2] as Answer2, [3] as Answer3
    FROM (
    SELECT question_id, user_id, answer
    FROM answer) P
    PIVOT
    (
    MAX (answer)
    FOR Question_id IN ([1], [2], [3])
    ) AS pvt

You get this result:

user_id Answer1         Answer2         Answer3
123 Answer 1        Answer AAAA      Answer BBBB
345 Answer CCCC     Answer DDDD      NULL
678 Answer EEEE     Answer FFFF      Answer SSSS
999 NULL                NULL         Answer RRRR

Possible? Yes.

Desirable? Rarely as you will run into lots of performance issues doing this entirely in sql. Once the number of columns in the pivot goes beyond around 8 or so, performance goes completely out the window.

Is this frought with legal issues? Unfortunately, yes. Microsoft in their infinite wisdom patented a "method and system for mapping between logical data and physical data", issued July 2007

So, proceed with caution.