How to turn data with pivot?-CodePudding

Data:

CASEID	VISITDATE	QUESTION	ANSWER	COMEFROM
1	2021-01-02	Q1	1	H
1	2021-01-02	Q2	2	O
1	2021-01-02	Q3	3	B
1	2021-01-08	Q1	4	H
1	2021-01-08	Q2	5	O
1	2021-01-08	Q3	6	B

Expected result:

CASEID	VISITDATE	Q1	Q2	Q3
1	2021-01-02	1	2	3
1	2021-01-08	4	5	6

My code:

SELECT CaseID, Visitdate, [Q1], [Q2], [Q3]

from

(
 select CaseID, Visitdate, Question, ANSWER, COMEFROM

 from DATA
          ) as v

pivot

(
 MAX(ANSWER) 
    
 FOR Question IN ([Q1], [Q2], [Q3])
                                    ) as p

OUTPUT:

CASEID	VISITDATE	Q1	Q2	Q3
1	2021-01-02	1	null	null
1	2021-01-02	null	2	null
1	2021-01-02	null	null	3
1	2021-01-08	4	null	null
1	2021-01-08	null	5	null
1	2021-01-08	null	null	6

CodePudding user response：

You can use below PIVOT code. When you do pivot , the columns not in the pivot are by default applied grouping.

declare @table table(CaseId int,    VisitDate   date, Question char(2), Answer int)

insert into @table values
(1  ,'2021-01-02','Q1',1)
,(1 ,'2021-01-02','Q2',2)
,(1 ,'2021-01-02','Q3',3)
,(1 ,'2021-01-08','Q1',4)
,(1 ,'2021-01-08','Q2',5)
,(1 ,'2021-01-08','Q3',6)

SELECT * FROM @table
PIVOT
(
MAX(Answer) FOR Question in ([Q1],[Q2],[Q3])
) as pvt

CaseId	VisitDate	Q1	Q2	Q3
1	2021-01-02	1	2	3
1	2021-01-08	4	5	6