CODEWITHSUNDEEP
sqlsql-servert-sql
Rob
Rob

Reputation: 59

Combine columns based on another column

We're running school reports and need to produce a table that adds in the students target grades.

Currently the targets have their own report cycle and all the other reports have separate cycles. I need to be able to produce a table that takes these targets and inserts them into the other cycles based on the student ID number, the subject name and the tutor name.

Here is the current setup:

cycle student Subject Tutor GradeName grade
1 123 Art ABC Target A
1 123 Maths CBA Target B
1 456 Art ABC Target D
1 456 Maths CBA Target C
2 123 Art ABC Attainment D
2 123 Maths CBA Attainment B
2 456 Art ABC Attainment A
2 456 Maths CBA Attainment B
3 123 Art ABC Attainment C
3 123 Maths CBA Attainment D
3 456 Art ABC Attainment B
3 456 Maths CBA Attainment D
4 123 Art ABC Attainment B
4 123 Maths CBA Attainment A
4 456 Art ABC Attainment A
4 456 Maths CBA Attainment A
5 123 Art ABC Attainment B
5 123 Maths CBA Attainment C
5 456 Art ABC Attainment D
5 456 Maths CBA Attainment B

and here is what I would like it to look like:

cycle student Subject Tutor GradeName grade
2 123 Art ABC Attainment D
2 123 Art ABC Target A
2 123 Maths CBA Attainment B
2 123 Maths CBA Target B
2 456 Art ABC Attainment A
2 456 Art ABC Target D
2 456 Maths CBA Attainment B
2 456 Maths CBA Target C
3 123 Art ABC Attainment C
3 123 Art ABC Target A
3 123 Maths CBA Attainment D
3 123 Maths CBA Target B
3 456 Art ABC Attainment B
3 456 Art ABC Target D
3 456 Maths CBA Attainment D
3 456 Maths CBA Target C
4 123 Art ABC Attainment B
4 123 Art ABC Target A
4 123 Maths CBA Attainment A
4 123 Maths CBA Target B
4 456 Art ABC Attainment A
4 456 Art ABC Target D
4 456 Maths CBA Attainment A
4 456 Maths CBA Target C
5 123 Art ABC Attainment B
5 123 Art ABC Target A
5 123 Maths CBA Attainment C
5 123 Maths CBA Target B
5 456 Art ABC Attainment D
5 456 Art ABC Target D
5 456 Maths CBA Attainment B
5 456 Maths CBA Target C

I have tried using OUTER APPLY to add the targets as a separate table but this adds them as a separate column. I have tried using COALESCE() but that only adds it to one cycle for some reason. Here is the code I am using for that (apologies for all the tables, the details are spread out on the database):

    SELECT 
        repCycle.TblReportsManagementCycleID AS 'cycle',
        pupilInfo.txtSchoolID AS 'student', 
        repStore.txtID AS 'subject',
        staffDets.Initials AS 'tutor',
        COALESCE(gradeTypes.txtGradingName, Target.txtGradingName) AS GradeName,
        COALESCE(repGrades.txtGrade, Target.txtGrade)  AS repGrade
    FROM iSAMS.dbo.TblReportsManagementCycle AS repCycle
    INNER JOIN iSAMS.dbo.TblReportsStore AS repStore
        INNER JOIN iSAMS.dbo.TblReportsStorePupilArchive AS pupilArchive
            INNER JOIN iSAMS.dbo.TblPupilManagementPupils AS pupilInfo
            ON pupilArchive.txtSchoolID = pupilInfo.txtSchoolID
        ON repStore.txtSchoolID = pupilArchive.txtSchoolID
        AND repStore.intReportCycle = pupilArchive.intReportCycle
        LEFT JOIN iSAMS.dbo.TblReportsStoreGrades repGrades
            LEFT JOIN iSAMS.dbo.TblReportsManagementTemplatesGrading AS gradeTypes
            ON repGrades.intGradeID = gradeTypes.TblReportsManagementTemplatesGradingID
        ON repStore.TblReportsStoreID = repGrades.intReportID
        LEFT JOIN iSAMS.dbo.TblStaff AS staffDets
        ON repStore.txtSubmitBy = staffDets.User_Code
    ON repCycle.TblReportsManagementCycleID = repStore.intReportCycle
    OUTER APPLY
    (SELECT 
        pupilInfo1.txtSchoolID,
        repStore1.txtID,
        repGrades1.txtGrade,
        rGradeTemplate.txtGradingName
    FROM TblReportsManagementCycle AS repCycle1
    INNER JOIN TblReportsStore AS repStore1
        INNER JOIN TblReportsStorePupilArchive AS pupilArchive1
            INNER JOIN TblPupilManagementPupils AS pupilInfo1
            ON pupilArchive1.txtSchoolID = pupilInfo1.txtSchoolID
        ON repStore1.txtSchoolID = pupilArchive1.txtSchoolID
        AND repStore1.intReportCycle = pupilArchive1.intReportCycle
        LEFT JOIN TblReportsStoreGrades repGrades1
            LEFT JOIN iSAMS.dbo.TblReportsManagementTemplatesGrading rGradeTemplate
            ON repGrades1.intGradeID = rGradeTemplate.TblReportsManagementTemplatesGradingID
        ON repStore1.TblReportsStoreID = repGrades1.intReportID
    ON repCycle1.TblReportsManagementCycleID = repStore1.intReportCycle
    LEFT JOIN TblStaff AS staffDets1
    ON repStore1.txtSubmitBy = staffDets.User_Code
    WHERE repStore1.intReportCycle = 163 -- TARGET CYCLE 
    AND pupilInfo1.txtSchoolID = pupilInfo.txtSchoolID
    AND repStore1.txtID = repStore.txtID
    AND staffDets1.Initials = staffDets.Initials
    ) AS Target

Any help to make the targets apply to every cycle would be greatly appreciated.

Thanks

Upvotes: 0

Views: 70

Answers (2)

Gudwlk
Gudwlk

Reputation: 1157

Here you can check by creating this table and inserting the below provided records which is taken from your first table:

CREATE TABLE dbo.TblReportsManagementCycle
(
 cycle INT  
 ,student 
 ,[Subject] VARCHAR(50) 
 ,Tutor VARCHAR(50) 
,GradeName VARCHAR(50)  
,grade VARCHAR(4)

)

Data Insert statements:

INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (1,123,'Art','ABC','Target','A');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (1,123,'Maths','CBA','Target','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (1,456,'Art','ABC','Target','D');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (1,456,'Maths','CBA','Target','C');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (2,123,'Art','ABC','Attainment','D');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (2,123,'Maths','CBA','Attainment','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (2,456,'Art','ABC','Attainment','A');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (2,456,'Maths','CBA','Attainment','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (3,123,'Art','ABC','Attainment','C');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (3,123,'Maths','CBA','Attainment','D');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (3,456,'Art','ABC','Attainment','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (3,456,'Maths','CBA','Attainment','D');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (4,123,'Art','ABC','Attainment','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (4,123,'Maths','CBA','Attainment','A');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (4,456,'Art','ABC','Attainment','A');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (4,456,'Maths','CBA','Attainment','A');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (5,123,'Art','ABC','Attainment','B');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (5,123,'Maths','CBA','Attainment','C');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (5,456,'Art','ABC','Attainment','D');
INSERT INTO  TblReportsManagementCycle (cycle,student,[Subject],Tutor,GradeName,grade) VALUES (5,456,'Maths','CBA','Attainment','B');

Answer for the question using simple join and UNION all with Derived tables.

SELECT 
     cycle
    ,Student
    ,[Subject]
    ,[tutor]
    ,[GradeName]
    ,[Grade] 
FROM 
(

    SELECT
         cycle
         ,Student
         ,[Subject]
         ,[tutor]
         ,[GradeName]
         ,[Grade]
     FROM  dbo.TblReportsManagementCycle
    WHERE Cycle <> 1

        UNION ALL  

     SELECT B.cycle
     ,A.Student
     ,A.Subject
     ,A.tutor
     ,A.GradeName
     ,A.Grade
      FROM  
        (
            SELECT 
                ROW_NUMBER() OVER (PARTITION BY cycle order by Cycle ASC) AS [row_nu]
                ,cycle
                ,student
                ,[subject]
                ,[tutor]
                ,[GradeName]
                ,[grade]
            FROM    dbo.TblReportsManagementCycle   
            WHERE  GradeName='Target' and cycle = 1 ) AS A 
                INNER JOIN  
            (   SELECT
                    ROW_NUMBER() OVER (PARTITION BY cycle order by Cycle ASC) AS [row_nu] 
                    ,cycle
                    ,student
                    ,[subject]
                    ,[tutor]
                    ,[GradeName]
                    ,[grade]

                FROM  dbo.TblReportsManagementCycle
                WHERE Cycle <> 1 ) AS B 
                ON A.row_nu = B.row_nu

    ) AS [T] 
 ORDER BY cycle,student,[Subject] ASC

Answer: Result Returned

Upvotes: 0

Jamiec
Jamiec

Reputation: 136074

I would do it by joining the student target and attained grades for each studentsubject/tutor combination and then union this together to get the right result

WITH joined
AS
(
 SELECT d.cycle, d.student, d.subject, d.tutor, d.grade as attainedGrade , t.grade as targetGrade
 FROM data d
 INNER JOIN
 (
    select * from data where gradename='Target'
 ) t
 ON d.student = t.student
 and d.subject = t.subject
 and d.tutor = t.tutor
 where d.gradename='Attainment'
)
SELECT * FROM (
  SELECT cycle, student, subject, tutor, 'Attainment' AS gradename, attainedGrade as grade
  FROM joined
  UNION ALL
  SELECT cycle, student, subject, tutor, 'Target' AS gradename, targetGrade
  FROM joined
) d
ORDER BY cycle, student, subject, tutor

Live example with your data: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=414acd73a004eb9d5ff57af7f4be6b63

Upvotes: 1

Related Questions