sql server语言 - sql Pivot显示用户问卷的回答

Question

我正在运行以下SQL语句

SELECT
 su.Id
  ,rp.QuestionId
  ,rp.AnswerId
FROM SiteUser su
  JOIN Response rp ON su.Id = rp.SiteUserId
  JOIN Answer an ON rp.AnswerId = an.Id

将产生以下输出

UserId    QuestionId    AnswerId
--------------------------------
1         1             1
1         2             6
2         1             2
2         2             3
3         1             2
3         2             2

我想实现以下输出

UserId    Question1   Question2
-------------------------------
1         1           6
2         2           3
3         2           2

谁能帮我写Pivot语句?

Answer 1

如果您使用的是RDBMS与PIVOT函数(SQL Server 2005+/Oracle)，那么您可以使用:

select Id, [1] As Question1, [2] as Question2
from
(
  SELECT su.Id
    ,rp.QuestionId
    ,rp.AnswerId
  FROM SiteUser su
  INNER JOIN Response rp 
    ON su.Id = rp.SiteUserId
  INNER JOIN Answer an 
    ON rp.AnswerId = an.Id
) src
pivot
(
  max(AnswerId)
  for QuestionId in ([1], [2])
) piv

如果你正在使用SQL Server，你有一个未知的问题id的值，那么你可以使用动态SQL来获得相同的结果:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT distinct ', ' + QUOTENAME(QuestionId) 
                    from Response 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
select @colsFinal = STUFF((SELECT distinct ', ' + QUOTENAME(QuestionId) + ' as Question'+QuestionId 
                    from Response 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT id, ' + @colsFinal + ' from 
             (
                SELECT su.Id
                  ,rp.QuestionId
                  ,rp.AnswerId
                FROM SiteUser su
                INNER JOIN Response rp 
                  ON su.Id = rp.SiteUserId
                INNER JOIN Answer an 
                  ON rp.AnswerId = an.Id
            ) src
            pivot 
            (
                max(AnswerId)
                for QuestionId  in (' + @colsPivot + ')
            ) p '
execute(@query)

Answer 2

由于您没有提到任何RDBMS，下面的查询将适用于几乎所有的RDBMS。

SELECT  su.Id,
        MAX(CASE WHEN rp.QuestionId = 1 THEN rp.AnswerId ELSE NULL END) Question1,
        MAX(CASE WHEN rp.QuestionId = 2 THEN rp.AnswerId ELSE NULL END) Question2
FROM    SiteUser su
        INNER JOIN Response rp 
            ON su.Id = rp.SiteUserId
        INNER JOIN Answer an 
            ON rp.AnswerId = an.Id
GROUP BY su.Id