How to convert rows into columns in SQL Server or in C# code behind-CodePudding

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.

CodePudding user response：

Going off of @R.Abbasi's recommendation for a pivot, here is how you can implement one:

with temp as (
    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
)
select *
from temp
pivot(max(answer) for question in ([Do you like to play?],[Do you have a car?],[Do you have a job?])) pt
where empID is not null
order by empID

I moved your current query into a CTE and then pivoted on that.

Just an FYI, your example data didn't have a "Yes" for Leo on "Do you have a job?", so my example will display a NULL instead of "Yes".

However, if your questions are going to be dynamic, you'll need to setup a dynamic pivot.

CodePudding user response：

You can use the pivot operator in SQL Server. If you want to know more about how pivot works, look at this link: https://docs.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?