CODEWITHSUNDEEP
sqlsql-serverdatabasepivotpivot-without-aggregate
Abu bakar
Abu bakar

Reputation: 49

Multiple rows into single row with header name

My problem is like this question but I am fetching data from different table:

Name   ID  Subject-name    obtained-marks
gumman  9   English              3
gumman  9   Islamic-Studies      4
gumman  9   Pak Studies          3

I want output like this:

ID   Name   English   Islamic-Studies   Pak-studies
9   gumman   3             4                3

How can I do this?

Upvotes: 1

Views: 896

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Try this:

Select s.ID, s.Name, 
       MAX(case WHEN s.SubjectName = 'English'         THEN s.ObtainedMarks end) as 'English',
       MAX(case WHEN s.SubjectName = 'Islamic-Studies' THEN s.ObtainedMarks end) as 'Islamic-Studies',
       MAX(case WHEN s.SubjectName = 'Pak Studies'     THEN s.ObtainedMarks end) as 'Pak-studies'
From Students s
GROUP BY s.ID, s.Name

DEMO

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select ID,
       Name,
       [English],
       [Islamic-Studies],
       [Pak Studies]
from YourTable 
pivot
  (
    min([obtained-marks]) for [Subject-name] in ([English],
                                                 [Islamic-Studies],
                                                 [Pak Studies])
  ) as P

Upvotes: 4

Related Questions