SQL Pivot on two dynamic columns

Question

I know there are a lot of PIVOT two Columns on stack overflow, but none seems to suit my needs :(

Here's the table that I have:

I want to pivot it to this:

And here's the initial setup:

CREATE TABLE TblPivot
(ID         INT IDENTITY(1, 1),
 Shop    VARCHAR(MAX),
 ElementId  VARCHAR(10),
 QuestionId VARCHAR(10),
 [Value]    VARCHAR(20)
);

GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem10','question1','one')
GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem11','question1','two')
GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem20','question2','1')
GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem20','question3','p1')
GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem21','question2','2')
GO
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) VALUES ('Shop1','elem21','question3','p2')

I suspect it must be something with CROSS-APPLY and PIVOT, but I am not sure on how to tackle this.

PS: The element ID can be null

Thanks!

Answer 1

Do the conditional aggregation:

select Shop,
       max(case when (QuestionId = 'question1' and ElementId = 'elem10')
                then value end) [question1- elem10],
       . . .
from TblPivot p
where QuestionId in ('question1', 'question2', 'question3') and
      ElementId in ('elem10', 'elem11', 'elem20', 'elem21')
group by Shop;