CODEWITHSUNDEEP
sqlsql-serverssrs-2012
CraigBob
CraigBob

Reputation: 157

How do I consolidate multiple rows into one

All,

Here's my Issue. It's giving a result set but I've got it across multiple rows and I want to put it into one row and get rid of the Nulls in the reading, Writing, Listening, Speaking, and Overall columns.

SQL:

Drop Table #STudentIDs
Declare @SchoolID  NVARCHAR(max) = '2152'
Declare @StudentID NVARCHAR(max) = '1451753'
Declare @TeacherID varchar(max) = '640470'

CREATE TABLE #STudentIDs 
        (
           STudentID INT primary key
        );

      INSERT INTO #StudentIDs
      SELECT Value
      FROM   dbo.Fnsplitid(@StudentID, ',');
      DECLARE @SchoolIDs TABLE
        (
           SchoolID INT
        );

      INSERT INTO @SchoolIDs
      SELECT Value
      FROM   dbo.Fnsplitid(@SchoolID, ',');

         DECLARE @RecentCELDTData TABLE
         (StudentID INT,
         Reading  NVarchar(20),
         Writing NVarchar(20),
         Listening NVarchar(20),
         Speaking NVarchar(20),
         OverallScore NVarchar(20),
         RecentTestDate DATE,
         Overall  BIT)
         INSERT INTO @RecentCELDTData

          SELECT S.StudentID,
          Max(CASE WHEN S.TestComponentCode='L0100' THEN S.TestPerformanceLevelCode ELSE '' END) AS Reading,
          Max(CASE WHEN S.TestComponentCode='L0700' THEN S.TestPerformanceLevelCode ELSE '' END) AS Writing,
          Max(CASE WHEN S.TestComponentCode='L0201' THEN S.TestPerformanceLevelCode ELSE '' END) AS Listening,
          Max(CASE WHEN S.TestComponentCode='L0202' THEN S.TestPerformanceLevelCode ELSE '' END) AS Speaking,
          Max(CASE WHEN S.TestComponentCode='L0800' THEN S.TestPerformanceLevelCode ELSE '' END) AS Overall,
          S.TestDate,
          S.IsSchoolLevelScoreEdit

          FROM (SELECT DISTINCT TD.IsSchoolLevelScoreEdit,STUTCS.StudentID,STUTCS.TestDate,TPLT.TestPerformanceLevelCode,TC.TestComponentCode,
          DENSE_RANK() OVER  (PARTITION BY STUTCS.StudentID ORDER BY stutcs.studentid,STUTCS.TestDate DESC) AS RNK
                       FROM #StudentIDs AS STUS 
               INNER JOIN StudentTestComponentScore(NOLOCK) AS STUTCS
                       ON STUS.StudentID=STUTCS.StudentID 
               INNER JOIN TestPurposeType(NOLOCK) AS TPT
                       ON STUTCS.TestPurposeTypeID=TPT.TestPurposeTypeID
                      -- AND TPT.TestPurposeName='Annual'
               INNER JOIN TestComponentPerformanceMapping(NOLOCK) AS TCPM
                       ON STUTCS.TestComponentPerformanceMappingID=TCPM.TestComponentPerformanceMappingID
               INNER JOIN TestComponent(NOLOCK) AS TC
                       ON TCPM.TestComponentID=TC.TestComponentID
                      AND TC.TestComponentCode IN ('L0100','L0700','L0201','L0202','L0800') 
               INNER JOIN TestScoreType(NOLOCK) AS TST
                       ON TCPM.TestScoreTypeID=TST.TestScoreTypeID
                      AND TST.TestScoreTypeCode='SCAL' 
               INNER JOIN dbo.TestPerformanceLevelType(NOLOCK) TPLT 
                       ON TCPM.TestPerformanceLevelTypeID = TPLT.TestPerformanceLevelTypeID
               INNER JOIN TestDefinition(NOLOCK) AS TD
                       ON TC.TestDefinitionID=TD.TestDefinitionID 
                      AND TD.TestDefinitionCode='CELDT'
                    ) AS S WHERE S.RNK=1  GROUP BY S.StudentID,S.TestDate,S.IsSchoolLevelScoreEdit,S.TestComponentCode,S.TestPerformanceLevelCode
Select * From @RecentCELDTData

Here's the results it gives me:

StudentID   Reading Writing Listening   Speaking    OverallScore    RecentTestDate  Overall
1451753     3-I                                                     2014-09-05      0
1451753                                 4-EA                        2014-09-05      0
1451753                                             4-EA            2014-09-05      0
1451753                     5-ADV                                   2014-09-05      0
1451753             5-ADV                                           2014-09-05      0

Here's what I need it to look like:

StudentID   Reading Writing Listening   Speaking    OverallScore    RecentTestDate  Overall
1451753     3-I     5-ADV   5-ADV       4-EA        4-EA            9/5/2014        0

What do I need to do to Modify the query to accomplish this?

Thanks

Upvotes: 0

Views: 89

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

you have S.TestDate and S.IsSchoolLevelScoreEdit in your GROUP BY. So it's probably giving you a record for each test date. Since your output column is called RecentTestDate, you could do a MAX on that column. I'm not sure what IsSchoolLevelScoreEdit means... but if you want to flag it so that if any of them are a one, you could do a max on it as well. The issue being that you can't do a max function on a bit column... so you'd have to convert it. So maybe something like this in your final insert statment?

    INSERT INTO @RecentCELDTData
    SELECT S.StudentID,
        Max(CASE WHEN S.TestComponentCode='L0100' THEN S.TestPerformanceLevelCode ELSE '' END) AS Reading,
        Max(CASE WHEN S.TestComponentCode='L0700' THEN S.TestPerformanceLevelCode ELSE '' END) AS Writing,
        Max(CASE WHEN S.TestComponentCode='L0201' THEN S.TestPerformanceLevelCode ELSE '' END) AS Listening,
        Max(CASE WHEN S.TestComponentCode='L0202' THEN S.TestPerformanceLevelCode ELSE '' END) AS Speaking,
        Max(CASE WHEN S.TestComponentCode='L0800' THEN S.TestPerformanceLevelCode ELSE '' END) AS Overall,
        max(S.TestDate) as RecentTestDate,
        convert(bit,max(convert(tinyint,S.IsSchoolLevelScoreEdit))) as Overall
    FROM 
        (SELECT DISTINCT TD.IsSchoolLevelScoreEdit,STUTCS.StudentID,STUTCS.TestDate,TPLT.TestPerformanceLevelCode,TC.TestComponentCode, DENSE_RANK() OVER  (PARTITION BY STUTCS.StudentID ORDER BY stutcs.studentid,STUTCS.TestDate DESC) AS RNK
        FROM #StudentIDs AS STUS 
        INNER JOIN StudentTestComponentScore(NOLOCK) AS STUTCS
        ON STUS.StudentID=STUTCS.StudentID 
        INNER JOIN TestPurposeType(NOLOCK) AS TPT
        ON STUTCS.TestPurposeTypeID=TPT.TestPurposeTypeID
        -- AND TPT.TestPurposeName='Annual'
        INNER JOIN TestComponentPerformanceMapping(NOLOCK) AS TCPM
        ON STUTCS.TestComponentPerformanceMappingID=TCPM.TestComponentPerformanceMappingID
        INNER JOIN TestComponent(NOLOCK) AS TC
        ON TCPM.TestComponentID=TC.TestComponentID
        AND TC.TestComponentCode IN ('L0100','L0700','L0201','L0202','L0800') 
        INNER JOIN TestScoreType(NOLOCK) AS TST
        ON TCPM.TestScoreTypeID=TST.TestScoreTypeID
            AND TST.TestScoreTypeCode='SCAL' 
        INNER JOIN dbo.TestPerformanceLevelType(NOLOCK) TPLT 
        ON TCPM.TestPerformanceLevelTypeID = TPLT.TestPerformanceLevelTypeID
        INNER JOIN TestDefinition(NOLOCK) AS TD
        ON TC.TestDefinitionID=TD.TestDefinitionID 
        AND TD.TestDefinitionCode='CELDT'
        ) AS S WHERE S.RNK=1  
    GROUP BY S.StudentID

Upvotes: 1

Related Questions