Taking multiple rows for a person to a single row

Rusty McLouth 1 Reputation point
2022-09-26T20:16:35.577+00:00

Hello,

New SQL person here.

Here is my SQL:
Select Distinct
studentfact.studentid,
campus,
sex,
race,
age,
financialaid,
degree,
enrollmentstatus,
startdate,
enddate,
CourseDimension.courseid,
coursegrade,

CASE When CourseDimension.courseid = 1 Then 'Lesson Planning'
WHEN CourseDimension.courseid = 2 Then 'Classroom Culture'
When CourseDimension.courseid = 3 Then 'Instruction'
When CourseDimension.courseid = 4 Then 'Assessing Student Learning'
ELSE 'Unknown' End as 'Course Name'

From
StudentFact Left Outer Join EnrollmentFact on StudentFact.studentid = EnrollmentFact.studentid
LEFT OUTER JOIN CourseGradeFact ON StudentFact.studentid = CourseGradeFact.studentid
LEFT OUTER JOIN QuestionResponseFact ON StudentFact.studentid = QuestionResponseFact.studentid
Left Outer Join CourseDimension on CourseGradeFact.courseid = CourseDimension.courseid
Left Outer Join QuestionDimension ON QuestionResponseFact.questionid = QuestionDimension.questionid,

Here is a sample of the data returned:

studentid campus sex race age financialaid degree enrollmentstatus startdate enddate Course Name coursegrade
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Instruction 2
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Classroom Culture 2.799999952
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Assessing Student Learning 3
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Instruction 3
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Lesson Planning 3
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Instruction 4
2 Texas F White 23 Receives Aid Master enrolled 7/1/2021 NULL Instruction 1.299999952
2 Texas F White 23 Receives Aid Master enrolled 7/1/2021 NULL Assessing Student Learning 2
2 Texas F White 23 Receives Aid Master enrolled 7/1/2021 NULL Lesson Planning 2.5
2 Texas F White 23 Receives Aid Master enrolled 7/1/2021 NULL Classroom Culture 2.700000048
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Classroom Culture 0.800000012
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Lesson Planning 1
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Assessing Student Learning 1.600000024
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Lesson Planning 2.200000048
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Instruction 2.400000095
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Lesson Planning 2.5
3 Texas F White 21 Receives No Aid Master enrolled 7/20/2021 NULL Lesson Planning 3

Here is the output I would like to see:

studentid campus sex race age financialaid degree enrollmentstatus startdate enddate Course Name coursegrade Course Name coursegrade Course Name coursegrade
1 Texas F White 24 Receives Aid Master enrolled 7/1/2020 NULL Instruction 2 Classroom Culture 2.799999952 Assessing Student Learning 3
2 Texas F White 23 Receives Aid Master enrolled 7/1/2021 NULL Instruction 1.299999952 Assessing Student Learning 2 Lesson Planning 2.5

Any suggestions would be greatly appreciated.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,825 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CosmogHong-MSFT 23,401 Reputation points Microsoft Vendor
    2022-09-27T06:04:37.577+00:00

    Hi @Rusty McLouth

    I just need a starting point on how to move variables from the rows into columns and how to avoid duplicates due to NULLs.

    It seems you need Aggregate calculation to turn rows to columns.
    Here is a sample to query the minimum score for each course. You can also change MIN to MAX to query the maximum score per course for each student. 

    CREATE TABLE #Sample (StudentID INT,Course_Name VARCHAR(50),Course_Grade FLOAT)  
INSERT INTO #Sample VALUES  
(1,'Instruction',2),  
(1,'Classroom Culture',2.799999952),  
(1,'Assessing Student Learning',3),  
(1,'Instruction',3),  
(1,'Lesson Planning',3),  
(1,'Instruction',4),  
(2,'Instruction',1.299999952),  
(2,'Assessing Student Learning',2),  
(2,'Lesson Planning',2.5),  
(2,'Classroom Culture',2.700000048)  
--DROP TABLE #Sample  
  
--Use Aggregate Function along with CASE WHEN  
SELECT StudentID,  
       MIN(CASE WHEN Course_Name='Assessing Student Learning' THEN Course_Grade ELSE NULL END)AS [Assessing Student Learning],  
	   MIN(CASE WHEN Course_Name='Classroom Culture' THEN Course_Grade ELSE NULL END)AS [Classroom Culture],  
	   MIN(CASE WHEN Course_Name='Instruction' THEN Course_Grade ELSE NULL END)AS [Instruction],  
	   MIN(CASE WHEN Course_Name='Lesson Planning' THEN Course_Grade ELSE NULL END)AS [Lesson Planning]  
FROM #Sample  
GROUP BY StudentID  
  
--Use PIVOT  
SELECT StudentID,[Assessing Student Learning],[Classroom Culture],[Instruction],[Lesson Planning]  
FROM #Sample  
PIVOT(MIN(Course_Grade)FOR Course_Name IN ([Assessing Student Learning],[Classroom Culture],[Instruction],[Lesson Planning]))P

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments