RE: Looking for an optimal solution

SSC Rookie

Points: 44

December 12, 2017 at 3:08 pm

#1971848

ScottPletcher - Tuesday, December 12, 2017 2:50 PM
Could you provide actual test data, as CREATE TABLE and INSERT statements, rather than just a picture of data. We can't write code against a picture .🙂

Yup sorry, it was at the top of the original post but definitely not a great format. Let me try again:
if exists (
    select [object_id]
    from sys.tables
    where [object_id] = object_id('dbo.TestData')
)
begin
    drop table dbo.TestData
end

create table TestData (
    StudentNumber int,
    Seq int,
    Subj nvarchar(50),
    ScoreA int,
    ScoreB int,
    ScoreC int
)

insert into TestData (
    StudentNumber,
    Seq,
    Subj,
    ScoreA,
    ScoreB,
    ScoreC
)
values
--student1
(123, 2, 'subject1', 2, 5, null), (123, 3, 'subject2', 20, 13, null), (123, 1, 'subject3', 23, 48, 39),
--student2
(345, 2, 'subject1', 20, 50, null), (345, 3, 'subject2', 200, 130, null), (345, 1, 'subject3', 230, 480, 390)

Michael L John - Tuesday, December 12, 2017 2:51 PM
What have you tried? Can you post that code?
A few questions.
Will there always be only 3 scores for a course for a student? In other words, there will be one row per student per course.

To answer you're question - yes, the number of score columns per subject will be static. Here's one example of a working solution I have (which feels unnecessarily complicated) - just tack this code underneath the test data bit right above ^:
select *
from TestData

declare @XML xml;
with distinctcandidates as (
    select distinct StudentNumber from TestData
)
select @XML = (
    select dc.StudentNumber as [@StudentID],
         (
            select Subj as [@subjname],
                 scorea,
                 scoreb,
                 scorec
            from TestData
            where StudentNumber = dc.StudentNumber
            order by seq
            for xml path ('Subject'), type
         ) as Subjects
    from distinctcandidates dc
    for xml path('Candidate'), root ('Candidates')
)

select @xml

select XCol.value('../@StudentID', 'int') as studentnumber,
    scorea1 = XCol.value('Subject[1]/scorea[1]','int'),
    scoreb1 = XCol.value('Subject[1]/scoreb[1]','int'),
    scorec1 = XCol.value('Subject[1]/scorec[1]','int'),
    scorea2 = XCol.value('Subject[2]/scorea[1]','int'),
    scoreb2 = XCol.value('Subject[2]/scoreb[1]','int'),
    scorec2 = XCol.value('Subject[2]/scorec[1]','int'),
    scorea3 = XCol.value('Subject[3]/scorea[1]','int'),
    scoreb3 = XCol.value('Subject[3]/scoreb[1]','int'),
    scorec3 = XCol.value('Subject[3]/scorec[1]','int')
from @XML.nodes('Candidates/Candidate/Subjects') AS XTbl(XCol)

Thanks for the continued interest

--MF