asked on

How to ge the column wise report from rowwise data.

I have an table mstmassstudents
where in there are fields
course,subjectcode,uid,ims,ems,total,grade.

This data get stored in this manner.

I want the output as desired. How to use pivot or any other method.

Sample Data attached.
Query-Data.xlsx

PortletPaul

SELECT
    UID
  , max(CASE WHEN SUBJECTCODE = 'CMP-126' THEN IMS   END) AS [CMP-126(IMS)]
  , max(CASE WHEN SUBJECTCODE = 'CMP-126' THEN EMS   END) AS [CMP-126(EMS)]
  , max(CASE WHEN SUBJECTCODE = 'CMP-126' THEN TOTAL END) AS [CMP-126(TOTAL)]
  , max(CASE WHEN SUBJECTCODE = 'CMP-126' THEN GRADE END) AS [CMP-126(GRADE)]
  , max(CASE WHEN SUBJECTCODE = 'CMT-121' THEN IMS   END) AS [CMT-121(IMS)]
  , max(CASE WHEN SUBJECTCODE = 'CMT-121' THEN EMS   END) AS [CMT-121(EMS)]
  , max(CASE WHEN SUBJECTCODE = 'CMT-121' THEN TOTAL END) AS [CMT-121(TOTAL)]
  , max(CASE WHEN SUBJECTCODE = 'CMT-121' THEN GRADE END) AS [CMT-121(GRADE)]
FROM mstmassstudents
GROUP BY
   UID

Open in new window

PortletPaul

to the above you may want to include:

WHERE SUBJECTCODE IN ('CMP-126','CMT-121')

searchsanjaysharma

ASKER

Fetching null, why we have put max.

PortletPaul

wrong post - sorry

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Grade C
why???

What grade should I award?

C should only be given for an incomplete solution that does not fully address or answer the question ... The asker must justify giving a C grade

@searchsanjaysharma I would very much appreciate knowing what was incomplete with my answer.

I shall request attention on this so it does not auto-close.