Agfdhyk

I need some assistance here. I have a result set as per below:

Name | IdNumber | Subject1 |Percentage | Subject2 | Percentage | Subject3 | Percentage | (...until subject 12)

I need to return only the 4 subjects and their percentage that the student scored the highest in.

Getting the highest mark per student is easy, I've done it this way:

SELECT [Other Fields],

  (SELECT Max(v) 

   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]

FROM [YourTableName]

Getting the other 4 is the problem.

Try to unpivot data as the data you have is not normalised and then pivot data again.

Preparing data:

DECLARE @FooTable TABLE 

( Name VARCHAR(10), IdNumber INT,

   Subject1 VARCHAR(100), Percentage1 INT,

   Subject2 VARCHAR(10),  Percentage2 INT,

   Subject3 VARCHAR(10),  Percentage3 INT,

   Subject4 VARCHAR(10),  Percentage4 INT

)



INSERT INTO @FooTable

(

    Name, IdNumber,

    Subject1, Percentage1,

    Subject2, Percentage2,

    Subject3, Percentage3,

    Subject4, Percentage4

)





 VALUES

(   'Name 1', -- Name - varchar(10)

    1,

    'Subject 1', -- Subject1 - varchar(10)

    10,  -- Percentage1 - int

    'Subject 2', -- Subject2 - varchar(10)

    20,  -- Percentage2 - int

    'Subject 3', -- Subject3 - varchar(10)

    30,  -- Percentage3 - int

    'Subject 4', -- Subject4 - varchar(10)

    40   -- Percentage4 - int

    )

, ('Name 2', -- Name - varchar(10)

    2,

    'Subject 1', -- Subject1 - varchar(10)

    20,  -- Percentage1 - int

    'Subject 2', -- Subject2 - varchar(10)

    30,  -- Percentage2 - int

    'Subject 3', -- Subject3 - varchar(10)

    40,  -- Percentage3 - int

    'Subject 4', -- Subject4 - varchar(10)

    50   -- Percentage4 - int)

    )

, ('Name 3', -- Name - varchar(10)

    3,

    'Subject 1', -- Subject1 - varchar(10)

    30,  -- Percentage1 - int

    'Subject 2', -- Subject2 - varchar(10)

    40,  -- Percentage2 - int

    'Subject 3', -- Subject3 - varchar(10)

    50,  -- Percentage3 - int

    'Subject 4', -- Subject4 - varchar(10)

    60   -- Percentage4 - int)

    )



SELECT * FROM @FooTable

Unpivoting data and pivoting data:

;WITH cte

AS (SELECT *,

           ROW_NUMBER() OVER (PARTITION BY Name ORDER BY percentage DESC) AS rn

    FROM

    (

        SELECT Name,               

               Percentage1,

               Percentage2,

               Percentage3,

               Percentage4

        FROM @FooTable

    ) p

        UNPIVOT

        (

            percentage

            FOR subject IN (Percentage1, Percentage2, Percentage3, Percentage4)

        ) up)

SELECT *

FROM (

SELECT cte.percentage, cte.subject

FROM cte

WHERE rn IN ( 1, 2, 3, 4 )

)source

PIVOT

(

    MAX(percentage)

    FOR subject IN ([Percentage1],[Percentage2],[Percentage3],[Percentage4])

)q

Output:

If you don't wish to change your table structure then you need to UNPIVOT the data after that you can select the top 4 subjects

;WITH cte AS (

SELECT *,ROW_NUMBER() OVER (PARTITION BY Name  ORDER BY Percentage DESC) AS rn

FROM 

(SELECT Name,percentage1,percentage2,percentage3,percentage4

            ,percentage5,percentage6,percentage7,percentage8

            ,percentage9,percentage10,percentage11,percentage12

FROM r ) p

UNPIVOT 

 (percentage  FOR subject IN (,percentage1,percentage2,percentage3,percentage4

                              ,percentage5,percentage6,percentage7,percentage8

                             ,percentage9,percentage10,percentage11,percentage12)

  ) up )

 SELECT * FROM cte

 WHERE rn IN (1,2,3,4)

The data structure is build in a way that makes things much more complicated as they could be.
You are not allowed to change that. Ok.
We need to do something ugly to deal with that ugly data structure.

Define a view that normalizes the structure. Then you can work in a normal way querying that view.

The view would look like:

select Name, IdNumber, Subject1 as Subject, Percentage1 as Percentage, 1 as SubjectNumber

union all

select Name, IdNumber, Subject2 as Subject, Percentage2 as Percentage, 2 as SubjectNumber

...

That should be much easier to deal with.

If this was for production somehow, you should try to set things straight much earlier.

Thank you Guys for all the assistance. one of the posts helped. but i cant find it now, i think they removed their post