Multiple rows into single row with header name

Question

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?

score 4 · Answer 1 · answered Apr 17 '12 at 06:58

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

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

2

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

edited Jan 18 '21 at 12:30

Community

1
1

answered Apr 17 '12 at 07:06

Mahmoud Gamal

78,257
17
139
164

thank you for your help, I see demo, but a little problem is there. imagine subject names are unknown, so how can do like that in demo – Abu bakar Apr 17 '12 at 07:44
@user1337945, If the list of subjects are unknows therfore the columns' headers are unknown, in theis case you should pivot the table data dynamically, and this is a common problem in sql server. Fortunately there are alot of workarounds for this. Just google for *SQL server PIVOT Table with dynamic columns* and you will find alot of useful threads like this: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx , and this: http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx – Mahmoud Gamal Apr 17 '12 at 07:51
thank you for helping me to manage my post. I solved my problem. first i fetch the related subjects from database and then comparing the the subject name in a loop using this statement ` MAX(case WHEN s.SubjectName = 'English' THEN s.ObtainedMarks end) as 'English',` and Concatenating into query. finally I found my result. – Abu bakar Apr 18 '12 at 06:43

Multiple rows into single row with header name

2 Answers2