0

I'm lost how to do this, I've tried the pivot answer on another post but doesn't work or I'm getting confused how to structure the query. The data from a multi join table.

Questions table - (questions are dynamic every session)

qid Question order
1 Do you like to play? 1
2 Do you have a car? 2
3 Do you have a job? 3

Participant table:

id empID participant surveyYr
1 e123 Tony 2021
2 e456 John 2021
3 e789 Leo 2021

Answers table (answers are nullable):

id pid Answer qid_fk
1 1 Yes 1
2 1 No 2
3 1 Yes 3
4 2 Yes 1
5 2 NULL 2
6 2 NULL 3
7 3 Yes 1
8 3 Yes 2
9 4 Yes 3

Query

Select 
    q.question, a.answer, p.empID, p.participant 
From 
    questions q 
Left Join 
    answers a on a.questionID_fk = q.qid
Left Join 
    participant p on p.id = a.pid

Output:

question answer empID participant
Do you like to play? Yes e123 Tony
Do you have a car? No e123 Tony
Do you have a job? Yes e123 Tony
Do you like to play? Yes e456 John
Do you have a car? NULL e456 John
Do you have a job? NULL e456 John
Do you like to play? Yes e789 Leo
Do you have a car? Yes e789 Leo
Do you have a job? Yes e789 Leo

Expected output after conversion:

EmpID participant Do you like to play? Do you have a car? Do you have a job?
e123 Tony Yes No Yes
e456 John Yes NULL NULL
e789 Leo Yes Yes Yes

Is this doable in T-SQL? Or is there a workaround on the code behind? I will be also using datatable js library to output this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CyberNinja
  • 872
  • 9
  • 25

1 Answers1

-1

You can use the pivot operator in SQL Server. If you want to know more about how pivot works, look at this link: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

For using pivot in c# linq queries, follow this question: Is it possible to Pivot data using LINQ?

R.Abbasi
  • 89
  • 10
  • I guess you are right but searching about Pivot would solve the issue. Those links were additional information – R.Abbasi Dec 11 '21 at 10:39