文本值上的 SQL 数据透视表 [英] SQL pivot table on text value

问题描述

我正在尝试在 SQL 中透视以下数据集.

I am trying to pivot the following set of data in SQL.

SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 40 -- M4
UNION
SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 80 -- M8

结果如下:

ParticipantID | QuestionnaireID  | Completed
1               40                 'Completed'
1               80                 'Not completed'
2               40                 'Completed'
2               80                 'Completed'
3               40                 'Completed'
3               80                 'Not completed'
4               40                 'Completed'
4               80                 'Not completed'

我希望结果像这样旋转(其中 M4 是 QuestionnaireID 40，M8 是 80):

What I would like is for the results to be pivoted like so (where M4 is QuestionnaireID 40 and M8 is 80):

ParticipantID | M4           | M8
1               'Completed'    'Not completed'
2               'Completed'    'Completed'
3               'Completed'    'Not completed'
4               'Completed'    'Not completed'

我正在为如何旋转桌子而苦苦挣扎.我当前的 SQL 如下并导致错误.我尝试根据其他帖子切换列，但无法确定需要什么:

I'm struggling on how to pivot the table. My current SQL is as follows and is resulting in errors. I've tried switching around the columns based on other posts but can't work out what is needed:

SELECT  *
FROM    (
    SELECT  ParticipantID,
            ContactReasonTypeID,
            Completed = CASE WHEN Returned IS NOT NULL
                            THEN    'Completed'
                            ELSE 'Not completed'
                        END
    FROM    ContactReason
    WHERE   ContactReasonTypeID = 40
    UNION
    SELECT  ParticipantID,
            ContactReasonTypeID,
            Completed = CASE WHEN Returned IS NOT NULL
                            THEN    'Completed'
                            ELSE 'Not completed'
                        END
    FROM    ContactReason
    WHERE   ContactReasonTypeID = 80
) AS tbl
PIVOT (
    MAX(Returned)
    FOR ContactReasonTypeID IN ([M4],[M8])
) AS pvt
ORDER BY ParticipantID

推荐答案

您可以使用命令 PIVOT 用于此

You can use the command PIVOT for this

DECLARE @t table(ParticipantID INT, QuestionnaireID INT, Completed varchar(20))
INSERT @t values
(1,40,'Completed'),(1,80,'Not completed'),(2,40,'Completed'),
(2,80,'Completed'),(3,40,'Completed'),(3,80,'Not completed'),
(4,40,'Completed'),(4,80,'Not completed')


SELECT ParticipantID, [40] M4, [80] M8
FROM @t
PIVOT
  (min(COMPLETED)  
FOR QuestionnaireID
  in([40],[80])  
)AS p
ORDER BY ParticipantID

结果:

ParticipantID   M4          M8
1               Completed   Not completed
2               Completed   Completed
3               Completed   Not completed
4               Completed   Not completed

这篇关于文本值上的 SQL 数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！