对于以下数据结构化的必需枢轴

Question

i具有以下格式的数据。

ID   question    answer
1    who         A
1    where       B
1    when        C
1    how         D

我需要以下的输出

id   who   where   when   how
1    A     B       C      D

Answer 1

您可以使用动态枢轴查询尝试。

create table #temp (ID int, question varchar(20), answer char(1))
insert into #temp values
(1,    'who',         'A'),
(1,    'where',       'B'),
(1,    'when',        'C'),
(1,    'how',         'D')
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.question) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT Id, ' + @cols + ' from 
            (
                select Id
                    ,question
                    , answer
                from #temp
           ) x
            pivot 
            (
                 max(answer)
                for question in (' + @cols + ')
            ) p '

execute(@query)

实时演示

Answer 2

使用条件聚合

  select id, 
  max(case when question='who' then answer end) as "who",
  max(case when question='where' then answer end) as "where",
  max(case when question='when' then answer end) as "when",
  max(case when question='how' then answer end) as "how"
   from table_name
  group by id

Answer 3

您可以尝试。

    DECLARE @column AS VARCHAR(MAX), @query  AS VARCHAR(MAX);
    SET @column = STUFF((SELECT distinct ',' + QUOTENAME(t.question) FROM table t FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
    set @query = 'SELECT Id, ' + @column + ' from 
                (
                    select Id, question, answer from table
                ) x
                pivot 
                ( max(answer) for question in (' + @column + ')
                ) p '

    exec (@query)