SQL Server 数据透视表

Question

我相当确定我可以使用 PIVOT 函数来解决我的问题，但我就是不明白。任何帮助将不胜感激。

所以我有一个看起来像这样的表

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

，我想编写一个返回结果集的查询，如下所示：

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

这可能吗？

TIA

Answer 1

假设表中存在此输入：

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'

您可以执行以下操作：

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

您会得到以下结果：

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

Answer 2

可能的？是的。

理想吗？完全在 sql 中执行此操作很少会遇到很多性能问题。一旦数据透视表中的列数超过 8 左右，性能就完全无法实现。

这是否充满法律问题？不幸的是，是的。微软以其无限的智慧获得了专利“逻辑数据与物理数据之间映射的方法和系统”，2007 年 7 月发布

因此，请谨慎行事。